Text copied!
NOT IN operator
In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data
SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.
NOT IN operator :
NOT IN operator is used to examine if a specific column doesn't match any of the values you specify in 'NOT IN' operator. It is similar to 'not equals to' ( != ) operator in SQL.
But, NOT IN operator enables you to specify a collection or list of values all at once to check if a specific column doesn't match any of the these list of values.
The syntax of the NOT IN operator generally looks like this :
SELECT column_name(s)
FROM table_name
WHERE column NOT IN (value1, value2, value3, value4, ...);
• Specify the column(s) name after the 'SELECT' keyword.
• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.
• Specify the table name after the 'FROM' keyword.
• Specify the conditions after the 'WHERE' keyword.
• Specify the list of values after the 'NOT IN' keyword.
Here's an example of how you might use the NOT IN operator :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].
[b] Employees_2 Table :
→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.
Case - 1 :
Let's assume you want to retrieve employee data from [Employees] table where Employee_Id doesn't contain value 1, 4, 5.
It can be achieved by combining 'not equals to' operator with 'OR' operator which is a very bad idea. Instead that you can use 'NOT IN' operator to keep code short, clean and efficient.
Run below SQL statement :
SELECT *
FROM [Employees]
WHERE Employee_Id NOT IN (1,4,5);
Above statement will retrieve employee data where Employee_Id doesn't contain value 1, 4, 5.
Case - 2 :
• You can also use 'SELECT' statement in 'NOT IN' operator to retrieve list of values from some other table.
Let's assume you want to retrieve employee data from [Employees] table where Employee_Id should not match the values found in the Employee_Id column of the [Employees_2] table.
Run below SQL statement :
SELECT *
FROM [Employees]
WHERE Employee_Id NOT IN ( SELECT Employee_Id FROM [Employees_2] );
Value 2, 3, 4 and 5 not present in the Employee_Id column in [Employees_2] table. Hence 4 row is returned.