How to use NOT IN operator in SQL queries?

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

The 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 work together? The “NOT IN” operators return the records that do not match the values given in the IN clause. The following example will make it clear.

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

An example of SQL NOT IN operator

In this example of using NOT IN operator, I am using the employee’s table that stores the information of employees including names, age 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 after using the NOT IN operator:

Query:

SQL NOT IN

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 NOT IN operator twice.

The first NOT IN is used for the same column as in above example. The second is used for the ID column which is a numeric column:

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:

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 whom employee IDs do not exist in the sto_emp_salary_paid table:

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 NOT IN operator.

The DISTINCT clause is used as sto_emp_salary_paid table contains duplicate employee ids.