SELECT-FROM-WHERE: The SQL Foundation

Concept. Every basic query has three clauses: FROM picks the table, WHERE keeps the rows that pass a test, SELECT chooses the columns to return.

Intuition. You write SELECT-FROM-WHERE, but it runs FROM, then WHERE, then SELECT: load all of Listens, drop the rows that fail the rating test, then keep just the columns you asked for.

SELECT-FROM-WHERE keeps some rows, then some columns. FROM Listens loads nine rows (listen_id, user_id, song_id, rating), colored by user. WHERE rating greater than 4.0 strikes the four failing rows (3.9, 3.9, 2.9, and the NULL which is UNKNOWN). SELECT user_id, song_id, rating projects the five survivors to three columns, dropping listen_id.

Figure 1. WHERE drops rows that fail its test, and SELECT keeps only the columns you name. FROM loads first, so the clauses run FROM, then WHERE, then SELECT. WHERE strikes the rows that fail its test (nine down to five, and a NULL comparison is UNKNOWN so it drops too), and SELECT keeps only the columns you name (dropping listen_id).

Basic Query Structure: The FloWS Order

-- Get listens with high ratings (above 4.0)
SELECT user_id, song_id, rating    -- choose only 3 columns for output
FROM Listens                       -- picks the table (9 rows)
WHERE rating > 4.0                 -- keeps rows where the test is TRUE

  1. Execution Order: FROM → WHERE → SELECT. Read it as FWS (FloWS mnemonic). That is the reverse of the SFW you write.

  2. Row Filtering: WHERE evaluates each row individually

  3. Column Selection: SELECT determines output columns

You write SELECT first, but the database runs it last: it needs the table (FROM) and the surviving rows (WHERE) before it can decide what to return (SELECT).

WHERE with AND and OR

-- AND: both conditions must hold
SELECT user_id, song_id, rating
FROM Listens
WHERE user_id = 1 AND rating > 4.0

-- OR: either condition is enough
SELECT user_id, song_id, rating
FROM Listens
WHERE user_id = 1 OR rating > 4.0

The same two checks (user_id = 1 and rating > 4.0) on nine rows. AND keeps a row only when both are true (2 rows, Mickey's high-rated listens). OR keeps it when either is true (6 rows). The NULL rating makes rating > 4.0 UNKNOWN, shown as a grey question mark.

Figure 2. AND keeps a row only when both checks pass, so two rows survive. OR keeps a row when either passes, so six survive. The NULL rating makes rating > 4.0 UNKNOWN, which neither AND nor OR can turn into a keep.

Understanding Aliases (AS Keyword)

An alias gives a column a new name in the output with AS. But because WHERE runs before SELECT, the alias does not exist yet when WHERE is evaluated, so WHERE cannot use it.

AS renames a column in the output: rating * 2 AS double_rating. But execution order is FROM, then WHERE, then SELECT, so the alias double_rating is created last, in SELECT. WHERE runs second, before the alias exists, so WHERE double_rating > 8 fails; WHERE rating * 2 > 8 works because the expression already exists.

Figure 3. AS renames a column in the result, but only in SELECT, which runs last. Since WHERE runs before SELECT, the alias does not exist yet there: WHERE double_rating > 8 fails, while WHERE rating * 2 > 8 works because the expression itself already exists.

Why It Matters:

Clause Execution Order Can Reference Cannot Reference
FROM 1st Table names SELECT aliases
WHERE 2nd Table columns SELECT aliases
SELECT 3rd Table columns, expressions Future aliases

SELECT with Calculations

SELECT can also compute new columns. rating * 2 AS double_rating doubles a value; CASE ... WHEN is SQL's if-then-else.

SELECT
    user_id,
    rating,
    rating * 2 AS double_rating,
    CASE
        WHEN rating >= 4.5 THEN 'Excellent'
        WHEN rating >= 4.0 THEN 'Good'
        ELSE 'Average'
    END AS category
FROM Listens
WHERE rating IS NOT NULL
user_idratingdouble_ratingcategory
14.59.0Excellent
14.28.4Good
13.97.8Average
24.79.4Excellent
34.99.8Excellent