Chapter 5 — Key Takeaways

The big idea

SELECT … FROM … WHERE … ORDER BY … LIMIT is the workhorse of SQL. FROM is the source relation, the SELECT list is projection (π), WHERE is selection (σ). Drill these until they're automatic — every chapter in Part II builds on them.

The shape

SELECT   columns / expressions / aliases     -- π : WHAT
FROM     table                               --     the relation
WHERE    condition                           -- σ : WHICH rows
ORDER BY columns [ASC|DESC] [NULLS FIRST|LAST]
LIMIT    n [OFFSET m];

Logical order of evaluation (memorize)

FROM → WHERE → SELECT → ORDER BY → LIMIT
  • WHERE runs before the SELECT list → it can't use SELECT-list aliases (repeat the expression).
  • ORDER BY runs after → it can use aliases.

WHERE toolkit

  • Comparisons: =, <>/!=, <, >, <=, >=.
  • Combine with AND / OR / NOT — AND binds tighter than OR; always parenthesize when mixing. (Case Study 1.)
  • BETWEEN a AND b (inclusive both ends).
  • IN (list) for "one of these" (cleaner than chained OR); beware NULL in NOT IN.
  • LIKE (% = any chars, _ = one char); ILIKE for case-insensitive (PostgreSQL).
  • IS NULL / IS NOT NULL — never = NULL.

Projection extras

  • Compute expressions (price * 1.08), concatenate (first_name || ' ' || last_name), rename with AS.
  • Name columns in code; reserve SELECT * for interactive exploration — * leaks new columns and couples output to table shape. (Case Study 2.)

Ordering & slicing

  • Rows have no inherent order — use ORDER BY to impose one.
  • DISTINCT dedupes the whole selected row (not just column 1); it isn't free.
  • LIMIT needs a deterministic ORDER BY. For pagination, end the sort with a unique column (the PK) or pages overlap and drop rows. (Case Study 2.)

Performance seed (theme #5)

The WHERE clause is where speed is won or lost: a filter can trigger a full scan or use an index — the 45 s vs. 12 ms difference. Detail in Chapters 23–24; for now, write correct queries and know how you filter will later matter.

You can now…

  • ☐ Write fluent SELECT/FROM/WHERE/ORDER BY/LIMIT queries.
  • ☐ Project, compute, and alias columns.
  • ☐ Filter with all the operators, parenthesizing mixed AND/OR.
  • ☐ Explain the evaluation order and alias visibility.
  • ☐ Use DISTINCT, ORDER BY (with null placement), and safe pagination.
  • ☐ Sanity-check result counts to catch silent logic bugs.

Looking ahead

Chapter 6 — JOINs. The single most important SQL skill. You'll connect Mercado's tables — INNER, LEFT, RIGHT, FULL, CROSS, and self-joins — building from a two-table join to a six-table business query (the book's JOIN-progression anchor).

One sentence to carry forward: A correct query isn't one that runs — it's one that answers the question you actually meant; parentheses, IS NULL, and a deterministic ORDER BY are how you make sure it does.