SQL NOT IN Vs NOT EXIST Operator

The difference between NOT IN and NOT EXIST operators will be discussed in this post.

SQL NOT IN Operator

In the WHERE clause, the SQL NOT IN command allows you to specify several values.

You can think of it as a set of NOT EQUAL TO instructions separated by an OR condition.

The NOT IN command compares particular column values from the first table to other column values in the second table or a subquery, and returns all values from the first table that aren't found in the second table, without filtering for different values.

The NOT IN command considers NULL as a value and returns it. NULL can't be compared to anything else, including other NULL s.

As a result, if the result set being probed returns a NULL, a NOT IN operation will fail. The result of a NOT IN query in this scenario is o rows.

Example: Let's look at Books Table

The following query execute Where ID is 2,3 is not exeucted. Because NOT IN Operator execute which value cannot be mentioned in query.

SELECT Year,Domain
FROM BookAdvance WHERE ID NOT IN ('ID',2,3);

In above query we cannot mentioned value of ID 1,4.

Output: The output of above query is

SQL NOT EXIST Operator

The SQL NOT EXISTS command is used to verify that certain values in a subquery are present.

The subquery does not provide any data; instead, it returns TRUE or FALSE results based on the existence of the subquery variables.

NULL can't be compared to anything else, including other NULL s.

As a result, if the result set being probed returns a NULL , a NOT IN operation will fail.

Example: The following 2 table Book and Order are help to analyze the NOT EXIST Operator

SELECT BID
FROM Book
WHERE NOT EXISTS (SELECT BID FROM Order WHERE Order.BID >Book. BID);

In above query both table have id of BID table. So we compare both column in book and order table

Output: The output will be

SQL Difference Between NOT IN And NOT EXIST Operator

Here , we discussed the distinguish in not in & not exist operator