---
title: "What is Difference between NOT IN vs NOT EXISTS?"  
description: "What is Difference between NOT IN vs NOT EXISTS?"  
author: "ICSM Computer"  
published: 2026-05-10  
updated: 2026-05-10  
canonical: https://www.mindstick.com/interview/34508/what-is-difference-between-not-in-vs-not-exists  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 2 minutes  

---

# What is Difference between NOT IN vs NOT EXISTS?

## `NOT IN` vs `NOT EXISTS` (Short Explanation)

| Feature | `NOT IN` | `NOT EXISTS` |
| --- | --- | --- |
| Purpose | Excludes values from a list | Checks if no matching row exists |
| NULL Handling | Fails if subquery contains `NULL` | Works correctly with `NULL` |
| Performance | Slower on large data | Usually faster |
| Recommended | Small/static lists | Large/production queries |

## Example

## `NOT IN`

```plaintext
SELECT *
FROM Employees
WHERE DepartmentId NOT IN (
    SELECT Id FROM Departments
);
```

Problem: If `Departments.Id` contains `NULL`, query may return no rows.

## `NOT EXISTS`

```plaintext
SELECT *
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM Departments d
    WHERE d.Id = e.DepartmentId
);
```

Safer and preferred approach.

## Best Practice

Use:

```plaintext
NOT EXISTS
```

for better performance and NULL safety.

## Answers

### Answer by ICSM Computer

## `NOT IN` vs `NOT EXISTS` (Short Explanation)

| Feature | `NOT IN` | `NOT EXISTS` |
| --- | --- | --- |
| Purpose | Excludes values from a list | Checks if no matching row exists |
| NULL Handling | Fails if subquery contains `NULL` | Works correctly with `NULL` |
| Performance | Slower on large data | Usually faster |
| Recommended | Small/static lists | Large/production queries |

## Example

## `NOT IN`

```plaintext
SELECT *
FROM Employees
WHERE DepartmentId NOT IN (
    SELECT Id FROM Departments
);
```

Problem: If `Departments.Id` contains `NULL`, query may return no rows.

## `NOT EXISTS`

```plaintext
SELECT *
FROM Employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM Departments d
    WHERE d.Id = e.DepartmentId
);
```

Safer and preferred approach.

## Best Practice

Use:

```plaintext
NOT EXISTS
```

for better performance and NULL safety.


---

Original Source: https://www.mindstick.com/interview/34508/what-is-difference-between-not-in-vs-not-exists

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.