NOT IN in SQL

The NOT IN operator is used to exclude records where a column's value matches any value in a given list or subquery.

? Syntax:

SELECT column1, column2, ...FROM table_nameWHERE column_name NOT IN (value1, value2, ...);

Or with a subquery:

SELECT column1, column2, ...FROM table_nameWHERE column_name NOT IN (    SELECT column_name FROM another_table WHERE condition);

? Example 1: Using a list of values

Get employees not in departments 10, 20, or 30:

SELECT employee_name, department_idFROM employeesWHERE department_id NOT IN (10, 20, 30);

? Example 2: Using a subquery

Get customers who do not have orders:

SELECT customer_id, customer_nameFROM customersWHERE customer_id NOT IN (    SELECT DISTINCT customer_id FROM orders);

?? Important Notes:

• If the list or subquery returns NULL, NOT IN might behave unexpectedly and return no rows. Use caution and consider alternatives like NOT EXISTS if NULLs are possible.

• Example safer alternative using NOT EXISTS:

SELECT customer_id, customer_nameFROM customers cWHERE NOT EXISTS (    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Let me know if you want me to explain differences between NOT IN and NOT EXISTS!