SELECT, FROM, WHERE

Return exactly the rows you need — and nothing else

Every table in beer_db has rows you do not need. The beers table has IPAs, stouts, lagers, and sours — but your report only asks for IPAs above 6% ABV. A SELECT without a WHERE returns everything. That is not a query — it is a table dump.\n\nWHERE is the most powerful clause in SQL. It determines correctness, not just performance. A WHERE condition that is slightly wrong returns slightly wrong data — and that mistake propagates silently through every report, dashboard, and export that follows.

Why WHERE Is the Most Important Clause

A SELECT without WHERE returns every row in the table. On a table with one million rows, that means transferring one million rows to the client — whether you need three of them or all of them. But performance is the smaller concern. Correctness is the real issue.

Column Projection: Only Ask for What You Need

-- ❌ SELECT * — returns every column, including blobs, TSV vectors, internal IDs
SELECT * FROM beers;

-- ✅ Project only what the report needs
SELECT name, style, abv, ibu
FROM beers;

SELECT * is dangerous in production for two reasons:

Schema changes (adding columns) silently change what your query returns
It wastes bandwidth returning computed columns like description_tsv that your application never uses

Filtering Rows with WHERE

SELECT name, abv
FROM beers
WHERE abv > 6.0;

Combining Conditions

-- AND: both conditions must be true
SELECT name, abv, ibu
FROM beers
WHERE abv > 6.0
  AND ibu < 50;

-- OR: at least one condition must be true
SELECT name, style
FROM beers
WHERE style = 'Stout'
   OR style = 'Porter';

NULL Comparisons Are Special

-- ❌ This returns nothing — NULL = NULL is NULL, not TRUE
WHERE ibu = NULL

-- ✅ Use IS NULL / IS NOT NULL
WHERE ibu IS NULL
WHERE ibu IS NOT NULL

Column projection

Specifying the exact columns to return in a SELECT. Projection reduces network traffic, prevents accidental exposure of sensitive columns, and makes queries robust against schema changes. SELECT * returns every column including generated columns, binary blobs, and audit fields that are rarely useful to the consumer.

Three-valued logic (TRUE / FALSE / NULL)

SQL comparisons have three possible results: TRUE, FALSE, or NULL (unknown). WHERE only passes rows where the condition is TRUE — not NULL. This is why WHERE abv = NULL never matches anything: the result is NULL, not FALSE or TRUE. Use IS NULL and IS NOT NULL to test for the absence of a value.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user. All queries in this lab run against the beers table.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

📋 Project Only What You Need

Run a SELECT that returns only the name, style, and abv columns from beers. Compare what you see to SELECT * — notice how much noise the full row contains.

SELECT name, style, abv FROM beers LIMIT 5;

🎯 Filter by ABV

Return beers with ABV greater than 7.0. Project name, style, and abv. Notice that abv has a NOT NULL constraint — no rows are silently excluded here.

SELECT name, style, abv FROM beers WHERE abv > 7.0 ORDER BY abv LIMIT 5;

⚡ Combine AND Conditions

Find beers with ABV between 5.5 and 7.0 (inclusive) AND with IBU below 50. Use AND to combine both conditions in a single WHERE clause.

SELECT name, abv, ibu FROM beers WHERE abv >= 5.5 AND abv <= 7.0 AND ibu < 50;

name | abv | ibu --------------------+------+----- Bold Bitter | 5.53 | 19 Premium Sour | 5.65 | 42 Seasonal Ale | 5.77 | 14 ...

👻 WHERE Silently Drops NULL Rows

Two beers in this table have no IBU recorded (a data import gap). Run SELECT COUNT(*) FROM beers to see the total, then run SELECT COUNT(*) FROM beers WHERE ibu > 0. The second number is lower — not because any beer has ibu = 0, but because ibu > 0 evaluates to NULL for those two rows, and WHERE silently discards them. No error. No warning.

SELECT COUNT(*) FROM beers;

SELECT COUNT(*) FROM beers WHERE ibu > 0;

-- Total rows: count ------- 400 (1 row) -- Rows where ibu > 0: count ------- 398 (1 row)

📣 Include NULL Rows with OR IS NULL

Run SELECT COUNT(*) FROM beers WHERE ibu < 200. You get 398 — the two NULL rows are still silently excluded, even though "any beer with unknown IBU" probably belongs in a report of all beers. Rewrite the query with OR ibu IS NULL to bring them back. The count should jump to 400.

SELECT COUNT(*) FROM beers WHERE ibu < 200;

SELECT COUNT(*) FROM beers WHERE ibu < 200 OR ibu IS NULL;

-- Excludes NULLs silently: count ------- 398 (1 row) -- Includes NULLs explicitly: count ------- 400 (1 row)

🕳️ Prove That = NULL Always Fails

Now try to find the two beers with missing IBU using WHERE ibu = NULL. You should get zero rows — even though the NULLs exist. This is three-valued logic: NULL = NULL evaluates to NULL, not TRUE, so no row ever passes.

SELECT name, abv, ibu FROM beers WHERE ibu = NULL;

name | abv | ibu ------+-----+----- (0 rows)

✅ Find the Missing IBU Data with IS NULL

Now use IS NULL — the only correct way to test for missing values. You should find exactly the two beers whose IBU was not recorded.

SELECT name, abv, ibu FROM beers WHERE ibu IS NULL;

name | abv | ibu ------------------+------+----- Anniversary Bock | 6.20 | Golden Stout | 6.80 | (2 rows)

Lab complete! You can now write precise, intentional SELECT queries:\n\n\n Projection (columns) : ✅ SELECT name, style, abv\n Filtering (rows) : ✅ WHERE abv > 6.0\n Multiple conditions : ✅ AND / OR with parentheses\n NULL awareness : ✅ IS NULL / IS NOT NULL\n

Enable JavaScript to run the live terminal and track your progress.