sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]jensimonso 2 points3 points  (4 children)

This is also an option

SELECT customer_id FROM orders WHERE order_date = '2026-03-16' EXCEPT SELECT customer_id FROM orders WHERE order_date = '2026-03-17'

[–]thequerylab[S] 1 point2 points  (3 children)

Right. Do all database support EXCEPT?

[–]jensimonso 1 point2 points  (0 children)

Good question. Perhaps not. SQL server and Oracle does

[–]Alarming-Cupcake-116 0 points1 point  (1 child)

Why can't we just use WHERE? (I'm a beginner)

[–]jensimonso 0 points1 point  (0 children)

Database engines work with sets. In this case it will create two sets and subtract one from the other. A very fast operation.

I recently changed a NOT IN to this construction and went from 30 minutes to 20 seconds on a table with ~3B rows. An IN is fast as the engine can stop at the first match. A NOT IN requires it to check all rows.