What are the main differences between NOT IN vs NOT EXISTS in SQL?
- How-Tos FAQs
- December 16, 2018
The SQL operator NOT IN and NOT EXISTS may seem similar at first glance, but there are differences between them.
Let us set up the tables ‘orders’ and ‘order_details’ as below:
CREATE TABLE orders (
order_id INT,
customer_name VARCHAR(100),
order_date DATETIME,
total_orders INT
);
INSERT INTO orders
SELECT 1, 'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19 UNION ALL
SELECT 3, 'Jack', '2020-02-03', 40 UNION ALL
SELECT 4, 'Rose', '2020-01-09', NULL ;
CREATE TABLE order_details (
order_detail_id INT,
order_id VARCHAR(100),
Item VARCHAR(100),
item_desc VARCHAR(200)
);
INSERT INTO order_details
SELECT 1, 1, 'laptop', NULL UNION ALL
SELECT 2, 2, 'mouse', 'Optical Mouse' UNION ALL
SELECT 3, 3, 'headphone', NULL UNION ALL
SELECT 4, 4, 'pendrive', '64 GB';The main disadvantage of NOT IN is that it does not support NULL value. Even if only a single value in the given data is NULL, the whole result will be empty. This is why NOT IN can be unpredictable and hence advised to avoid using if there is the NULL value or there is the possibility of having a NULL value in the future.
SELECT * FROM orders o
WHERE o.customer_name NOT IN ('Jack', NULL);
#Output
Returns nothingNOT EXISTS can handle the NULL value. In fact, it does not care what data is selected in the subquery. The subquery only returns TRUE or False. It returns TRUE if it returns any row and returns FALSE if it does not return any row.
SELECT * FROM orders o
WHERE EXISTS (
SELECT od.item_desc
FROM order_details od
WHERE od.order_id = o.order_id
AND o.total_orders <5
);
#Output
# order_id customer_name order_date total_orders
--------------------------------------------------------------------------
1 Jack 2020-02-03 00:00:00 4NOT IN can be used to compare a column with some hardcoded value, but it is not possible to use hardcoded values in NOT EXISTS.
-- Valid Query
SELECT * FROM customers
WHERE customer_name NOT IN ('Jack');
#Output
# customers_id customer_name email
-----------------------------------------------------------------
2 Jully jack@email.com
3 John john@email.com
4 Rose rose@email.com
-- Invalid Query
SELECT * FROM customers
WHERE NOT EXISTS ('Jack');
#Output
ERRORUp Next:
Read What are the main differences between UNION and UNION ALL in SQL?
