A database table is really just a spreadsheet. Rows are records — one row
per customer, one row per order. Columns are the fields — a name, a date,
an amount. SQL is the language you use to ask that spreadsheet questions:
show me these rows, combine these two sheets, add these up.
The five things below aren't about learning more SQL words. They're about
five specific moments where a question you ask gets answered technically
correctly, but not in the way you meant — and nothing warns you. No error
message. Just a wrong number that looks right.
1. Asking for "everyone" and getting "only some people"
Say you have a table of customers and a table of orders. You want a list
of every customer, showing their order if they have one, and nothing if
they don't — because you still want to see the customers with no orders.
sql
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';
The LEFT JOIN part does say "keep everyone from the customers list."
But the WHERE line underneath quietly overrides it. Here's why: a
customer with no order gets a blank (called NULL) where their order date
would be. And "is this blank date after 1 January" doesn't have a yes or
no answer — it's neither true nor false, it's just undefined. SQL treats
undefined the same as no, so that customer gets thrown out.
You asked for everyone. You got only the people who also happen to have a
recent order. Nothing crashed. Nothing warned you. The list is just
quietly shorter than you think it is.
The picture makes this easier to see than the sentence does:
The fix is to move the date condition up into the matching step instead
of the filtering step:
sql
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.order_date >= '2026-01-01'
Now the date check only decides which order to attach, not whether the
customer gets kept.
2. A "give me everyone except these people" list that comes back empty
sql
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM cancelled_orders);
Plain English: show me every customer who isn't in the cancelled-orders
list. Reasonable ask.
Here's the trap. If even one row in cancelled_orders has a blank
customer_id — maybe a cancellation that was logged before anyone was
assigned to it — the whole comparison breaks. SQL can't say "is this
customer definitely not equal to a blank," so it refuses to say yes for
anyone at all. The entire result becomes empty, for a reason that has
nothing to do with your actual customers.
Swap it for asking the question a different way — not "is my ID absent
from that list," but "does a cancelled order exist for me":
sql
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM cancelled_orders x
WHERE x.customer_id = c.customer_id
);
This version isn't confused by a blank value sitting somewhere else in
the list. If you don't know for certain that a column can never contain a
blank, treat this as the safer way to ask the question.
3. Two ways of counting that quietly disagree
sql
SELECT COUNT(*) AS all_rows, COUNT(phone_number) AS with_phone
FROM customers;
Counting sounds like it should only have one right answer. It doesn't.
COUNT(*) counts every row, full stop. COUNT(phone_number) counts only
the rows where that specific field actually has something in it —
blanks don't get counted.
If those two numbers come back different, that gap is telling you exactly
how many customers have no phone number on file. It's a genuinely useful
one-line check for "how messy is this data" before you build anything
more complicated on top of it.
4. Adding things up in order, and getting the order wrong
sql
SELECT
order_date,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;
This is meant to build a running total — day one's number, then day one
plus day two, then day one plus two plus three, and so on. Most of the
time it does exactly that.
The exception: if two rows share the exact same date, some databases
default to treating tied dates as one combined step rather than adding
them one at a time. Your running total can jump differently than expected
right at the point where a date repeats, and it's easy to miss because
the rest of the total looks completely normal.
Being explicit fixes it:
sql
SUM(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
This spells out "add strictly one row at a time," which removes the
ambiguity about what to do with ties.
5. A family tree that's missing the person at the top
sql
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
This is a table joined to itself, to line up each employee with their
manager's name. It works for almost everyone — except whoever has no
manager at all, like the CEO or the founder. That one row has a blank
where the manager should be, and a plain join treats a blank as "doesn't
match," so that person disappears from the results entirely.
You'll get a org chart that looks complete and is quietly missing exactly
one row — the one at the very top.
sql
LEFT JOIN employees m ON e.manager_id = m.employee_id
Same lesson as the very first pattern: whenever a match can land on a
blank, start with LEFT and only switch to a plain join once you're sure
you want to lose that row on purpose.
Every one of these has the same shape. Something in the data is blank,
or two rows tie, and the ordinary-sounding question you asked gets
answered in a way that's technically consistent but not what you meant.
None of it shows up as an error. It shows up as a number that's wrong in
a way nobody notices until later.
More worked examples like these, free to browse with no signup, are at
the https://www.prepnplaced.com/prepnplaced-notes.
Top comments (0)