How to use NOT IN operator in SQL queries?

What is SQL NOT IN operator?

In order to understand how “NOT IN” works in SQL, you should understand the IN and NOT operators.

The SQL IN operator is the shorthand for multiple OR conditions where you may specify one or more values.

For example:

IN (10, 20, 30)
The NOT operator is used to return the records if the given condition is not True.

For example:

NOT >= 20

So, how to use NOT and IN to work together?

The “NOT IN” operators return the records that do not match the values given in the IN clause.

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:

Query:

SELECT * FROM store_db.dbo.sto_employees

WHERE emp_name NOT IN ('Jimmy', 'Shaun', 'Ben');

SQL NOT IN

Note: You may use the NOT IN operator with SELECT, UPDATE, and DELETE command in SQL.

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);

SQL NOT IN AND

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');

SQL NOT IN JOIN

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)

SQL NOT IN SELECT

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.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!