What is SQL NOT IN operator?
In order to understand how “NOT IN” works in SQL, you should understand the IN and NOT operators.
So, how to use NOT and IN to work together?
The following example should make it clear.
An example of SQL NOT IN operator
In this example of using the “NOT IN” operator, I am using the employee’s table that stores the information of employees including names, age,s etc.
By using the NOT IN operator, we will return all records from the table except with employee names Jimmy, Shaun, and Ben.
Have a look at the full data and data after using the NOT IN operator:
SELECT * FROM store_db.dbo.sto_employees WHERE emp_name NOT IN ('Jimmy', 'Shaun', 'Ben');
Using two columns and NOT IN twice
See the following query where I used conditions for two columns. The AND operator is used in the WHERE clause with the NOT IN operator twice.
The first NOT IN is used for the same column as in the above example. The second is used for the ID column which is a numeric column:
SELECT * FROM store_db.dbo.sto_employees WHERE emp_name NOT IN ('Jimmy', 'Shaun', 'Ben') AND id NOT IN (1,3,5,7);
The example of NOT IN with JOIN clause
In this example, a JOIN query is used for combining the matched records of two tables. The NOT IN operator is used to filter the records of a few employee names as shown below:
SELECT * FROM sto_employees SE LEFT JOIN sto_orders SO ON SE.id=SO.emp_id WHERE SE.emp_name NOT IN ('Mike', 'John', 'Ben');
Using values in NOT IN clause from another table
This query specifies the values for the NOT IN operator from another table by using the SELECT statement. The employees’ table is related to the sto_emp_salary_paid table that stores the employee’s salaries paid every month.
Both tables are related by employee IDs. The condition is to not return results whose employee IDs do not exist in the sto_emp_salary_paid table:
SELECT * FROM store_db.dbo.sto_employees WHERE id NOT IN (SELECT DISTINCT(emp_id) FROM sto_emp_salary_paid)
In the graphic, the first table shows the data for the sto_employees table. The second displays the data for sto_emp_salary_paid and the third one is the result of our query with the “NOT IN” operator.
The DISTINCT clause is used as sto_emp_salary_paid table contains duplicate employee IDs.