Select a value where it doesn't exist in another table

sql

not-exists

sql-performance

sql-optimization

byAlex Kataev·Sep 8, 2024

Quickly find rows in table1 that aren't present in table2:

SELECT *
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t2.matching_column = t1.matching_column
    -- This line here is the party-crasher; it shows up and spoils the 'exists' fun.
);

Or via LEFT JOIN where the join isn't filled:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.matching_column = t2.matching_column
WHERE t2.matching_column IS NULL;
-- This line feels like my fridge. Everything interesting is NOT there!

Both NOT EXISTS and LEFT JOIN... IS NULL filter out the unmatched rows.

Performance Showdown: NOT EXISTS vs LEFT JOIN

If you're wrestling with large datasets, the referee here would be efficiency. When it comes to heavyweight performances, NOT EXISTS tends to out-wrestle NOT IN or LEFT JOIN, especially when the underdog subquery has teeth... I mean a large result set. Shield yourself with proper indexing to fend off performance drop kicks.

Prepare to ring the bell when:

NOT EXISTS stops at the first match, while LEFT JOIN...IS NULL wrestles with all possible pairings before nailing down the final pin (the WHERE clause).
NOT IN can belly flop if there are NULL values in the column comparison. Ensure non-nullability or code in null checks to dodge rope burns.

Alternative Strategies: EXCEPT and NOT IN

Call in the EXCEPT clause

EXCEPT, the unexpected tag team partner, only returns rows unique to the first query:

SELECT ID
FROM table1
EXCEPT
SELECT ID
FROM table2;
-- Epic ultimate wrestling match! Who's unique to table1? Find out after this SQL query!

Square off with NOT IN

When you can guarantee your columns won't go ghost (i.e., they're non-nullable), step into the ring with NOT IN:

SELECT *
FROM table1
WHERE ID NOT IN (SELECT ID FROM table2);
-- Is it in? Is it out? Does it exist or not? Tune in next time on "Keeping up with the Columns.”

Note that an unexpected NULL value can make this round hit below the belt and leave your result set empty.

The title matchup: Visualisation

Time to step into our SQL wrestling arena, where we have two celebratory fruit baskets Basket_A and Basket_B to visualize the action:

Basket_A 🧺: [🍏, 🍊, 🍇]
Basket_B 🧺: [🍊, 🥝]

The match!? Find any exclusive fruit from Basket A not in Basket B!

SELECT Fruit
FROM Basket_A
WHERE NOT EXISTS (
    SELECT 1
    FROM Basket_B
    WHERE Basket_A.Fruit = Basket_B.Fruit
);
-- And in this corner, weighing in at a lean 1KB, the undisputed, exclusive, fruit of Basket_A!

And in the SQL wrestling ring,the only fruits left standing are our exclusive 🍏 and 🍇.

SQL Server 2000: A Blast from the Past

If you're trying to implement these moves on a space-time wrestling mat such as SQL Server 2000, you might find yourself in a bit of a pickle. The modern SQL maneuvers and techniques we're spoiling you with may tap out when confronted with these classic gladiators.

Remember: Review your SQL Server version's capabilities, consult the appropriate Microsoft documentation, and shuffle through community discussions to ensure your SQL powers don't become a pile of dust.

Maintaining SQL Championship: Data integrity and maintainability

Keeping your database in championship shape requires a commitment to maintainability and database integrity:

Use tried-and-true SQL patterns for maintainability. They're like your gym routine, keeping your queries lean and robust.
Jot down comments to aid in explaining your intentions, especially when coding NOT EXISTS subqueries. It’s the weightlifting journal for your SQL gym!
Regularly reviewing your database schema helps you stay in your peak performance stage. It’s the routine check-up for your SQL diet!

Smoking Gun References

NOT IN vs NOT EXISTS - Stack Overflow — Get ringside seats to the thrilling match of NOT EXISTS vs LEFT JOIN WHERE NULL.
Execution Plan Basics - Simple Talk — Learn to optimize SQL queries like a World Heavyweight champ with this execution plan guide.
MySQL :: MySQL 8.0 Reference Manual :: 15.2.15 Subqueries — The official MySQL playbook on subqueries.
sql - NOT IN vs NOT EXISTS - Stack Overflow — Dig into StackOverflow's debate on the NOT IN vs NOT EXISTS main event.