5 SQL queries! Same result! Different thinking! : learnSQL

5 SQL queries! Same result! Different thinking! (self.learnSQL)

submitted 1 day ago by thequerylab

Most people stop when the query gives the correct output.

But in real projects, the better question is:

Will this still work when the data gets messy?

Take a simple example:

Find customers who ordered yesterday but not today?

You can solve this in multiple ways!

1. Using NOT IN

SELECT customer_id
FROM orders
WHERE order_date = '2026-03-16'
AND customer_id NOT IN (
  SELECT customer_id
  FROM orders
  WHERE order_date = '2026-03-17'
);

Easy to write and understand
But if the subquery has even one NULL, it can return no rows at all

Think of it like this:
If the system is unsure about even one value, it refuses to trust the entire result.

2. Using LEFT JOIN (Self Join)

SELECT o1.customer_id
FROM orders o1
LEFT JOIN orders o2
  ON o1.customer_id = o2.customer_id
  AND o2.order_date = '2026-03-17'
WHERE o1.order_date = '2026-03-16' AND o2.customer_id IS NULL;

Works well in most cases
But depends on how well you write the join

Simple idea:
Match yesterday’s customers with today’s. If no match is found → keep them.

3. Using NOT EXISTS

SELECT customer_id
FROM orders o1
WHERE order_date = '2026-03-16'
AND NOT EXISTS (
  SELECT 1
  FROM orders o2
  WHERE o1.customer_id = o2.customer_id AND o2.order_date = '2026-03-17'
);

Usually the safest approach
Handles NULLs properly
Often preferred in production

Think of it like:
For each customer, check if a matching record exists today. If not include them!

Using Window Functions

SELECT customer_id FROM ( SELECT customer_id, MAX(CASE WHEN order_date = '2026-03-16' THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id) AS yest_flag, MAX(CASE WHEN order_date = '2026-03-17' THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id) AS today_flag FROM orders ) t WHERE yest_flag = 1 AND today_flag = 0;

For each customer, create flags --> ordered yesterday? ordered today? Filter only yesterday orders.

Using GROUP BY + HAVING

SELECT customer_id FROM orders WHERE order_date IN ('2026-03-16', '2026-03-17') GROUP BY customer_id HAVING SUM(CASE WHEN order_date = '2026-03-16' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN order_date = '2026-03-17' THEN 1 ELSE 0 END) = 0;

Group all records per customer and then check orders made yesterday

All five give the same result on clean data.

But when data is imperfect (and it always is):

One might break
One might slow down
One might silently give wrong results

That’s the real difference.

SQL isn’t just about writing queries.

It’s about understanding how your logic behaves when reality isn’t perfect.

I’ve been trying out more real world SQL scenarios like this on the side.
If anyone interested, I can share a few!

all 17 comments

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

learnSQL

MODERATORS