How to SELECT data from a table which is not present in another table with SQL
- How-Tos FAQs
- December 17, 2018

How to SELECT data from a table which is not present in another table with SQL is a common operation in databases. There are many ways to query this, like using NOT IN and NOT EXISTS. But the most efficient way is to use LEFT JOIN.
CREATE TABLE basket1(
id INT IDENTITY(1,1),
fruit VARCHAR(20)
);
CREATE TABLE basket2(
id INT IDENTITY(1,1),
fruit VARCHAR(20)
);
INSERT INTO basket1
SELECT 'apple' UNION ALL
SELECT 'orange' UNION ALL
SELECT 'grapes' UNION ALL
SELECT 'avocado'
INSERT INTO basket2
SELECT 'pear' UNION ALL
SELECT 'grapes' UNION ALL
SELECT 'apple' UNION ALL
SELECT 'Mango'
-- List of fruits in basket1 but not in basket2
-- Step 1: LEFT JOIN
SELECT b1.*,b2.*
FROM basket1 b1
LEFT JOIN basket2 b2
ON b1.fruit = b2.fruit
-- Output
-- Here, all the data of the left table (basket1) are returned
-- but in case of the right table (basket2) only common data is returned
-- and for the data which in not present in the right table is shown as NULL
-- like in the row 1, 'apple' exists only in the left table so, right table
-- has NULL and since 'grapes' exists on both tables so its value is shown in
-- both tables
-- basket1 -- -- basket2 --
id fruit idfruit
----------------------------------
1 apple NULL NULL
2 orange NULL NULL
3 grapes 2 grapes
4 avocado NULL NULL
-- In the above, output if we select only the rows with NULL in right table we
-- will have the data which exists only in the left table but not in the right
-- table.
-- Step 2: select only rows of right table with NULL value
SELECT b1.*
FROM basket1 b1
LEFT JOIN basket2 b2
ON b1.fruit = b2.fruit
WHERE b2.fruit IS NULL
-- Output
id fruit
-----------
1 apple
2 orange
4 avocado
-- For the list of fruits in basket2 but not in basket1
SELECT b2.*
FROM basket2 b2
LEFT JOIN basket1 b1
ON b1.fruit = b2.fruit
WHERE b1.fruit IS NULL
-- Output
id fruit
-----------
1 pear
3 apple
4 Mango
And that’s how to SELECT data from a table which is not present in another table with SQL!
Up Next: