How to use NOT IN operator in SQL queries?

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:

Query:

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.