SQL 02 — Reading Data

Roughly 90% of the SQL a production database executes is SELECT — and every findByName you wrote in Spring Boot 04 compiled down to one. This module teaches you to read and write them directly, and to survive the trap that has shipped more silently-wrong reports than any other feature in SQL: NULL.

The Goal

By the end of this module you can:

• Explain why SQL is declarative — you describe the result, the planner finds the path — and why that’s the deepest difference from a Java loop
• Replay the logical evaluation order (FROM → WHERE → SELECT → ORDER BY → LIMIT) and use it to predict the classic alias-in-WHERE error before psql shows it
• Filter with =, <>, IN, BETWEEN, LIKE/ILIKE and say which rows survive without running the query
• Predict what NULL does to any comparison — and diagnose the NOT IN trap that returns zero rows with zero errors
• Sort and page with ORDER BY, LIMIT, OFFSET, and state honestly where OFFSET pagination lies to users
• Map a JPA derived query method to the exact WHERE clause it generates

The Lesson

Declarative: you describe the result, the planner finds the path

In Java, you tell the machine how: loop over expenses, if the amount is big enough, add to a list, sort the list, take three. Every step is yours.

In SQL, you describe what:

SELECT description, amount_paise
FROM expense
WHERE amount_paise >= 100000
ORDER BY amount_paise DESC
LIMIT 3;

You never said “loop.” You never said “check each row.” PostgreSQL’s query planner reads your description and invents the steps itself — maybe a full scan, maybe an index jump, maybe something you’ve never heard of. Same query, different plan next month when the table is 100x bigger. That’s the deal: you give up control of the how and gain a planner that’s smarter than your hand-written loop. You inspect its decisions with EXPLAIN — that’s module 06, the lie detector.

This is the single biggest mental shift coming from Java. Stop thinking “what does it do first.” Start thinking “what set of rows did I describe.”

The logical order — and the error every beginner hits

SQL is written in one order and evaluated in another. The evaluation order is the key that unlocks half the language’s weird behavior:

flowchart LR
    A[FROM pick the table] --> B[WHERE filter rows]
    B --> C[SELECT compute columns]
    C --> D[ORDER BY sort]
    D --> E[LIMIT cut]

Now watch the most common beginner error in SQL:

SELECT description, amount_paise / 100 AS rupees
FROM expense
WHERE rupees > 1000;

ERROR:  column "rupees" does not exist
LINE 3: WHERE rupees > 1000;

It looks insane — the alias is right there. But replay the order: WHERE runs before SELECT. At filtering time, rupees hasn’t been computed yet. It does not exist. The fix is to repeat the expression:

WHERE amount_paise / 100 > 1000

And here’s the proof the order is real: ORDER BY rupees works, because ORDER BY runs after SELECT, when the alias exists. One alias, legal in one clause, illegal in another — entirely explained by the pipeline above. Memorize the pipeline, not the error.

WHERE — the predicates

Everything in WHERE is a predicate: an expression that’s TRUE, FALSE, or (hold this thought) NULL for each row. Only rows where it’s TRUE survive.

Two honest footnotes:

• BETWEEN with timestamps bites. created_at BETWEEN '2026-06-01' AND '2026-06-02' includes June 2 at exactly midnight and nothing after. For date ranges, professionals write the half-open form: created_at >= '2026-06-01' AND created_at < '2026-06-03'.
• MySQL trap worth one line: MySQL’s LIKE is case-insensitive by default; Postgres’s is not. People moving between them get burned in both directions.

And the teaser you’ll thank me for in module 06: ILIKE '%trip' — a pattern starting with % — works fine, returns correct rows, and can never use an index. The database has no choice but to read every row, forever, no matter what indexes you add. Park that.

NULL — the three-valued minefield

NULL is not a value. It means unknown / absent. And comparisons with unknown produce unknown:

SELECT NULL = NULL;

 ?column?
----------

(1 row)

Not true. Not false. Blank — NULL. “Is one unknown thing equal to another unknown thing?” The only honest answer is “unknown.” SQL has three truth values: TRUE, FALSE, NULL — and WHERE keeps only TRUE. Rows where the predicate is NULL are dropped just as hard as FALSE.

So WHERE some_column = NULL matches nothing, ever — even rows where the column genuinely is NULL, because NULL = NULL is NULL, not TRUE. The correct test is a dedicated operator:

WHERE some_column IS NULL       -- the only correct way
WHERE some_column IS NOT NULL

Now the centerpiece trap of this module. Walk it slowly, because in production it doesn’t error — it returns an empty result that looks like a finished job.

SELECT name FROM friend WHERE id NOT IN (1, 3, NULL);

Zero rows. Even friend 2 and friend 4, who are plainly not 1, not 3. Expand what NOT IN actually means for friend 2:

id <> 1   AND   id <> 3   AND   id <> NULL
TRUE      AND   TRUE      AND   NULL
= NULL                                      → not TRUE → row dropped

Every row hits that id <> NULL term. Every row’s predicate collapses to NULL. Every row is dropped. One NULL inside a NOT IN list silently empties the entire result — no error, no warning, no hint. In real life the NULL doesn’t come from you typing it; it comes from a subquery over a column you didn’t check. The fixes:

-- fix 1: exclude NULLs in the subquery
WHERE id NOT IN (SELECT friend_id FROM some_table WHERE friend_id IS NOT NULL)

-- fix 2 (what experienced people reach for): NOT EXISTS, which is NULL-proof
WHERE NOT EXISTS (SELECT 1 FROM some_table t WHERE t.friend_id = friend.id)

Plain IN degrades more gently (a NULL in the list just can’t match), but NOT IN is the booby trap. You’ll detonate it on purpose in Build This.

Last NULL tool — COALESCE returns its first non-NULL argument:

SELECT COALESCE(NULL, NULL, 'fallback');   -- → fallback

Its everyday job is display: turning a NULL into '—' or 0 at the last moment. You’ll lean on it hard in module 03, where LEFT JOINs manufacture NULLs by design.

ORDER BY — multiple keys and where NULLs land

SELECT description, paid_by, amount_paise
FROM expense
ORDER BY paid_by ASC, amount_paise DESC;

Sort keys apply left to right: group by payer, and within each payer, biggest first — exactly like a Java Comparator.comparing(...).thenComparing(...).

NULLs need a home in any sort. Postgres defaults: ASC puts NULLs last, DESC puts NULLs first — which means a “biggest first” sort on a nullable column shows the unknowns at the top, exactly where you don’t want them. Override explicitly:

ORDER BY some_nullable_thing DESC NULLS LAST;

LIMIT and OFFSET — and the pagination lie

SELECT description, amount_paise
FROM expense
ORDER BY created_at DESC
LIMIT 5 OFFSET 5;       -- "page 2"

First honesty point: LIMIT without ORDER BY returns arbitrary rows. Not “the first 5” — whichever 5 the planner produced first, which can change between runs. A LIMIT only means something on top of an ORDER BY.

Second honesty point — page drift: OFFSET counts rows at query time. If a new expense lands between a user loading page 1 and clicking page 2, every row shifts by one — the last row of page 1 reappears as the first row of page 2, or a row is skipped entirely. Users report it as “I saw the same item twice.” OFFSET pagination is built to drift on live data. And it has a second cost: OFFSET 10000 makes the database produce and throw away 10,000 rows to give you 20. The grown-up alternative — keyset pagination, WHERE id > last_seen ORDER BY id LIMIT 20 — needs module 06’s indexes to shine, so it waits there. For now: know that OFFSET is the easy choice, not the correct one.

DISTINCT — sometimes a feature, sometimes a confession

SELECT DISTINCT paid_by FROM expense;

Legitimate: “which friend ids have ever paid?” — collapsing duplicates is the question itself.

But when you see SELECT DISTINCT bolted onto a complex query, ask: why were there duplicates at all? Nine times out of ten the answer is a join that multiplied rows, and DISTINCT is masking it instead of fixing it. That smell gets a full autopsy in module 03.

CASE — computed display columns

CASE is SQL’s if/else-if, usable anywhere an expression goes:

SELECT description,
       CASE
         WHEN amount_paise >= 500000 THEN 'big'
         WHEN amount_paise >= 100000 THEN 'medium'
         ELSE 'small'
       END AS size
FROM expense;

First matching WHEN wins, top to bottom. No ELSE and nothing matches? The result is NULL — the minefield again, by design.

What Hibernate does with all of this

Every derived query method you wrote in Spring Boot 04 is a code generator for exactly this module:

List<Expense> findByPaidByAndCreatedAtAfter(Long paidBy, Instant after);
// → SELECT ... FROM expense WHERE paid_by = ? AND created_at > ?

List<Expense> findByDescriptionContainingIgnoreCase(String s);
// → ... WHERE description ILIKE '%' || ? || '%'   ← leading wildcard. Module 06 will hurt.

GreaterThan → >. Between → BETWEEN. Containing → wrapped LIKE. The method name is a tiny DSL that compiles to a WHERE clause — which means every trap on this page exists in JPA too, wearing a Java costume. Pass null into findByName(name) and Hibernate emits name = NULL: zero rows, no error — the exact three-valued logic you just learned, one layer up. (Spring Data has IsNull keywords for a reason.)

Check The Concept

How This Shows Up At Work

• The report that shipped empty for a month. A nightly job emails “friends with no settled expenses” using NOT IN (SELECT ...). One row in the subquery’s column goes NULL after an unrelated feature launch — the email silently becomes empty, and nobody notices because an empty report doesn’t look broken. The fix is one IS NOT NULL; finding it takes the person who knows the expansion.
• The duplicate-row bug ticket. “User saw the same transaction on page 2 and page 3.” That’s OFFSET drift during live inserts, not a frontend bug — and knowing that saves a week of blaming the React table component.
• The code-review catch. A teammate writes WHERE status <> 'CANCELLED' on a nullable column and the rows with NULL status vanish from the report. The reviewer who asks “what should NULL status do here?” is doing senior work with junior syntax.
• The interview staple. “Why doesn’t NULL = NULL return true?” and “what does NOT IN do with a NULL?” are two of the most common SQL screen questions in Indian product-company interviews — precisely because they separate people who’ve run queries from people who’ve read about them.

Build This

Open psql against your database from module 01: psql -U postgres -d splitease_sql.

1. Reset the seed so your output matches mine exactly (ids matter today):

TRUNCATE expense, friend RESTART IDENTITY CASCADE;

INSERT INTO friend (name) VALUES ('Asha'), ('Rohit'), ('Darshan'), ('Meera');

INSERT INTO expense (description, amount_paise, paid_by, created_at) VALUES
  ('dinner',        300000, 1, '2026-06-01 20:30+05:30'),
  ('cab',            60000, 2, '2026-06-01 23:10+05:30'),
  ('homestay',      840000, 1, '2026-06-02 09:00+05:30'),
  ('groceries',     145000, 3, '2026-06-05 18:45+05:30'),
  ('petrol',         90000, 2, '2026-06-06 08:15+05:30'),
  ('movie tickets', 100000, 1, '2026-06-07 19:00+05:30');

2. Rung 1 — everything. SELECT * FROM expense; — six rows. Read every column once; this is the raw material.

3. Rung 2 — filter. Expenses of ₹1000 or more: SELECT description, amount_paise FROM expense WHERE amount_paise >= 100000; — four rows; cab and petrol are gone.

4. Rung 3 — compute. Add a rupee column: SELECT description, amount_paise / 100 AS rupees FROM expense WHERE amount_paise >= 100000;

5. Rung 4 — the full ladder. Filter, compute, classify, sort, cut:

SELECT description,
       amount_paise / 100 AS rupees,
       CASE
         WHEN amount_paise >= 500000 THEN 'big'
         WHEN amount_paise >= 100000 THEN 'medium'
         ELSE 'small'
       END AS size
FROM expense
WHERE created_at >= '2026-06-01'
ORDER BY amount_paise DESC
LIMIT 3;

 description | rupees |  size
-------------+--------+--------
 homestay    |   8400 | big
 dinner      |   3000 | medium
 groceries   |   1450 | medium
(3 rows)

Before moving on, narrate the pipeline out loud: FROM loaded six rows, WHERE kept six, SELECT computed two columns, ORDER BY sorted, LIMIT cut to three.

6. Break it on purpose #1 — alias in WHERE. Change the ladder’s WHERE to WHERE rupees > 1000 and run it. Read the error: column "rupees" does not exist. Say why in one sentence (WHERE runs before SELECT), then fix it back to the amount_paise form.

7. Break it on purpose #2 — the NOT IN landmine. Simulate a refund batch imported from a CSV with one bad row:

CREATE TEMP TABLE refund_batch (friend_id BIGINT);
INSERT INTO refund_batch VALUES (1), (2), (NULL);

SELECT name FROM friend
WHERE id NOT IN (SELECT friend_id FROM refund_batch);

Zero rows. Darshan and Meera should obviously be there. Diagnose it the way you would in production: SELECT count(*) FROM refund_batch WHERE friend_id IS NULL; → 1. There’s your culprit. Fix it both ways — add WHERE friend_id IS NOT NULL to the subquery, then rewrite with NOT EXISTS — and confirm both return Darshan and Meera. Then DROP TABLE refund_batch;.

8. Break it on purpose #3 — the index killer (preview). SELECT description FROM expense WHERE description ILIKE '%stay'; — works, returns homestay, feels innocent. Write this in your notes: a pattern starting with % forces a full-table read forever. Module 06 proves it with EXPLAIN.

Where to Practice

Check Yourself

1. Recite the logical evaluation order. Which two steps explain the alias-in-WHERE error?
2. Why can ORDER BY use a SELECT alias when WHERE can’t?
3. What does NULL = NULL evaluate to, and what does WHERE do with rows whose predicate evaluates to that?
4. A NOT IN list contains one NULL. What does the query return, and walk the expansion that explains it.
5. What does COALESCE(a, b, c) return?
6. LIMIT 10 with no ORDER BY — which ten rows?
7. Name both problems with OFFSET pagination on live data.
8. You spot SELECT DISTINCT glued onto a four-table query in review. What’s your first question?

Explain it out loud: Explain to an empty chair why WHERE id NOT IN (1, 3, NULL) returns nothing — expand the predicate term by term for one concrete row, name the three truth values, and finish with what WHERE does to a NULL. If you can’t do the expansion from memory, reread the minefield section.

Still Unclear?

Build me a three-valued logic truth table for AND, OR, and NOT with the values
TRUE, FALSE, NULL. Then quiz me: give me five WHERE predicates over rows
containing NULLs and make me predict TRUE, FALSE, or NULL for each before
you reveal the answer. Don't run them for me.

I come from Java where I write loops that say HOW to compute a result. SQL is
declarative. Explain what the PostgreSQL planner actually does between
receiving my SELECT and returning rows, and why the same query can use a
different strategy as the table grows. No query plans yet — concepts only.

Compare OFFSET pagination and keyset pagination for an expense feed that
gets new inserts every second. Show me the exact user-visible bug OFFSET
causes, then challenge me to explain why keyset avoids it.

Why AI Can’t Do This For You

AI will write any SELECT you describe, instantly and usually correctly. But a query that returns wrong-but-plausible rows looks identical to a right one — and the NOT IN trap’s whole signature is an empty result that resembles a finished job. The person who replays the logical order in their head and asks “what does NULL do here?” catches it; the person who trusts generated SQL ships an empty report for a month.

Your production data has NULLs the prompt never mentioned, in columns nobody documented. AI can’t see them. The three-valued reflex — every comparison can be TRUE, FALSE, or unknown — is a habit you install by detonating the trap yourself, which is exactly what Build This made you do.

Module done? Add it to today’s tracker