What is NOT IN Operator in the SQL Query?
FREE
The NOT IN SQL query or the NOT IN operator is used to filter the result if the values that are mentioned in the IN operator are not satisfied. That means the NOT IN SQL query check the given expression or the column name against the values. If there is a match then the SELECT statement will not return that particular record in the output of the SQL query.
The NOT IN operator is the exact opposite of the IN operator in SQL. Also, NOT is a logical operator in SQL and you can put this operator before any conditional statement to select the rows for which the statement is false. Similarly, when the NOT IN operator is used with the WHERE clause, the values that are defined in the WHERE clause are excluded in the result of the NOT IN SQL query. There are examples below which explain the usefulness of the NOT IN SQL queries.
What is the Use of NOT IN Operator in SQL query?
As you already know, the IN operator in SQL allows you to specify the list of values that are mentioned in the WHERE clause and are included in the results. Similarly, the NOT IN operator is used to not include those values that are mentioned in the WHERE clause.
Another usage of the NOT IN SQL query is to replace the group of arguments that are using the <> or != operator that are combined with an AND operator. This will improve the readability of the code and hence easier to understand and debug.
Consider the below query to understand the above statement.
The above query is harder to read and it's a long query to achieve a result that can be done in a simpler way. Another approach would be to use the NOT IN SQL query. The syntax of the NOT IN SQL query is given below.
Syntax
The following SQL query explains the usage of the NOT IN operator. The last three statements of the query which has a Sales table, are converted using a single NOT IN SQL query and are given below.
This query is working the same as that of the above-mentioned long query but this is shorter and easier to read. As it can be seen from the differences in the queries that the NOT IN operator replaces the <> or != operator. The working of the NOT IN SQL query is already explained above, but that would be hard to imagine the working of the same. But these all should make more sense when you'll see the examples below.
Examples of NOT IN SQL Query
Now, let's take some examples of the NOT IN SQL query to understand how the NOT IN SQL query works when applied to fetch the records from a database table.
Example
Consider the below given Students table for the below given example only. In this example, the SQL query is applied to fetch only the student results for those who are not residents of Chicago city.
Students:
| student_no | student_name | student_age | student_city | stu_percentage |
|---|---|---|---|---|
| 1 | Jason Myers | 19 | New York | 80 |
| 2 | Luke Benjamin | 21 | Chicago | 95 |
| 3 | Aaron Wilson | 18 | San Antonio | 71 |
| 4 | Robert Banks | 20 | Chicago | 76 |
| 5 | Jason Ortiz | 21 | New Orleans | 83 |
Here is the SQL query which contains the NOT IN operator and explains how you can use this operator to fetch the records from the database.
The result of the above SQL query is given below:
Output:
In the above example, the NOT IN SQL query is used with the WHERE clause. The above example explains how you can use the NOT IN keyword with the WHERE clause. The result of the above query includes all the students' records whose city is not in 'Chicago'.
Now, let's take another example of the NOT IN operator which involves working with numbers.
Example:
Consider the below Sales table for the below given example only. In this example, we need to look for the employees who have made any number of sales but not 10 sales or 35 sales.
Sales:
| employee_id | employee_name | employee_age | employee_sales |
|---|---|---|---|
| 10 | Regina Johnson | 39 | 19 |
| 13 | Cynthia Stokes | 35 | 13 |
| 15 | Jemy Sarrano | 32 | 10 |
| 17 | Kiara Guzman | 31 | 35 |
| 19 | Mia Wilson | 36 | 20 |
Here is the SQL query that fetches the required records from the database table.
Output:
In the above example, the NOT IN SQL query is used with the HAVING clause. The above example explains how you can use the NOT IN keyword with the HAVING clause. The result of the above query includes all the employees' records whose sales are not exactly the '10' and '35'.
See Also:
Conclusion
- The NOT IN SQL query or the NOT IN operator is used to filter the result if the values that are mentioned in the IN operator are not satisfied.
- The NOT IN operator is the exact opposite of the IN operator in SQL.
- The usage of the NOT IN SQL query is to replace the group of arguments which are using the <> or != operator that is combined with an AND operator.
- When the NOT IN operator is used with the WHERE clause, the values that are defined in the WHERE clause are excluded in the result of the NOT IN SQL query.