Not in VS No exists in SQL server

Yesterday we got an issue, customer complain that XYZ application not showing any data. Even we did not do any things. I mean we had not deleted any records in fact we have inserted few records in the table. My junior team member is looking that he said the ‘Bagesh’ we have data in all required table but still we are not getting any data. Yesterday all are fine.

First let’s mimic this issue and then we will see why not our query is returning the data.

For the demo we are creating tables and inserting few records in this table.

CREATE TABLE Employees (     EmpID INT,     EmpName VARCHAR(50),     DeptID INT );  CREATE TABLE Departments (     DeptID INT ); --Inserting few records INSERT INTO Employees VALUES (1, 'Bagesh', 10), (2, 'Rajesh', 20), (3, 'Mahesh', NULL), (4, 'Pari', 30);  INSERT INTO Departments (DeptID) VALUES (10), (20)

 Below we my query

select * from Employees select * from Departments SELECT * FROM Employees WHERE DeptID NOT IN (SELECT DeptID FROM Departments);

 See the result

This was my query. It is working perfectively.

One day someone insert record in the Departments table with null values as below

INSERT INTO Departments (DeptID) VALUES (null)

Now running the same query and let’s see

Oh! Now we are not getting any records. Because the subquery contains NULL.This was my issue.

SQL logic: DeptID NOT IN (10, 20, NULL)

Comparison with NULL = UNKNOWN, and SQL Server returns nothing.

This is the biggest limitation of NOT IN.

While IN is working lets the below

 Check value against entire list at once Fails if ANY NULL exists. If the subquery used with NOT IN returns even one NULL value, the query returns no rows at all. If the Departments table contains a NULL value, SQL Server cannot compare values properly, and the result becomes UNKNOWN. This is why NOT IN is considered dangerous in real-time systems.

To overcome with issue, we will below approach

Ø  Either we will use not null check in the inner query which is not recommend.

Ø  Instead of sub query we will use not Exists

Not Exits is working find see the below query

SELECT e.* FROM Employees e WHERE NOT EXISTS (     SELECT 1     FROM Departments d     WHERE d.DeptID = e.DeptID );

See the result

NOT EXISTS checks row-by-row. NULL values do not break logic. It only checks if a match exists.

Checks row-by-row Stops as soon as a match is found Safe with NULLs. It handles NULL values safely. SQL Server can stop checking as soon as a match is found. It usually performs better on large tables. In production systems, NOT EXISTS is always preferred.

 We can fix NOT IN by filtering NULL values

SELECT * FROM Employees WHERE DeptID NOT IN (     SELECT DeptID     FROM Departments     WHERE DeptID IS NOT NULL );

But still, we are not getting the correct result. EmpID =3 is missing. Due to this in real projects, this adds risk and complexity. So the industry standard is simple: use NOT EXISTS.

NOT IN fails when the subquery returns NULL values, while NOT EXISTS works correctly and performs better on large datasets, which is why NOT EXISTS is preferred in real-time SQL Server systems.