SQL - Not In vs Not Exists

I have stumble upon this great website talking about difference between “NOT IN” vs “NOT EXISTS” in SQL.

I’ve follow the step and create same table structure with same sample data into my temporary ms sql table.

Create temp table.

CREATE TABLE #EMP_MASTER
(
EMP_NBR INT NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR(20),
MGR_NBR INT NULL
)

Populate sample data

INSERT INTO #EMP_MASTER VALUES (1, ‘DON’, 5)
INSERT INTO #EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO #EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO #EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO #EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO #EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO #EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO #EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO #EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO #EMP_MASTER VALUES (10, ‘JIM’, 5);

tbl1

The aim of this query is to find all those employees who are not managers. The person is consider manager when MGR_NBR field is NULL.

tbl2

hmm… query looks ok but the result is not.

The problem is because of the way how NULL value is been handle which is exist in mgr_nbr field. Change the query like below and we have the expected result.

tbl3

We also can change the query to :

tbl4

Using not exists clause return the expected result, but I think the query look like more complicated.

tbl5

Based on execution plan (I’m not sure if im reading the execution plan correctly,correct me if im wrong), I can say that Not Exists clause will be much faster than Not In clause.