AI Hints
Homeโ€บSQL Tutorialโ€บCTEs, EXISTS, and Set Operations
SQL ยท Lesson 60 of 126

EXISTS

Is there at least one?.

โฑ 2 min readโ–ถ Video at 1:25:29๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Sometimes you don't care how many matching rows there are. You just want to know, is there at least one? Has this customer ever ordered? Has this book ever been bought in bulk? Exists answers yes or no.

EXISTS
From the video: EXISTS
  • Is there at least one?
  • Has this customer ordered?
  • EXISTS says yes or no

NOT EXISTS

Not exists keeps a customer when the subquery finds no orders for them. Inside, select one is a habit. Exists only cares whether a row is found, not what's in it. Run it. Customers with no orders, the same people we found with a left join.

query.sqlsql
SELECT c.first_name, c.last_name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1
                  FROM orders AS o
                  WHERE o.customer_id = c.customer_id)
LIMIT 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ first_name โ”‚ last_name โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Daniel     โ”‚ Ali       โ”‚
โ”‚ Daniel     โ”‚ Ali       โ”‚
โ”‚ Nina       โ”‚ Silva     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Bought in bulk

Which books have ever been bought three or more at a time? Exists checks for at least one order line with a quantity of three or more. Run it. Here are five of them. Exists stops looking as soon as it finds one match, so it's quick.

query.sqlsql
SELECT b.title
FROM books AS b
WHERE EXISTS (SELECT 1
              FROM order_items AS oi
              WHERE oi.book_id = b.book_id
                AND oi.quantity >= 3)
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚          title           โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard       โ”‚
โ”‚ Code of the River        โ”‚
โ”‚ Stars Over Lagos         โ”‚
โ”‚ A Short History of Bread โ”‚
โ”‚ Python for Curious Minds โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Exists asks, is there at least one matching row? Not exists asks, is there none? Next, stacking query results on top of each other.

Key takeaways
  • EXISTS: at least one
  • NOT EXISTS: none
  • SELECT 1 is a habit
Next up: UNION, INTERSECT, and EXCEPT โ€” combining results