SELECT-FROM-WHERE: The SQL Foundation

Concept. A basic query has three clauses. FROM chooses the table. WHERE keeps rows that make its predicate TRUE. SELECT chooses the output columns.

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. FROM produces nine Listens rows. WHERE keeps the five rows where rating > 4.0 evaluates to TRUE and drops rows where it evaluates to FALSE or UNKNOWN, including the NULL rating. SELECT returns only user_id, song_id, and rating and drops 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 when both predicates evaluate to TRUE, so two rows survive. OR keeps a row when either predicate evaluates to TRUE, so six rows survive. The NULL rating makes rating > 4.0 evaluate to UNKNOWN, and UNKNOWN does not satisfy WHERE.

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. Then the same filter two ways: WHERE double_rating > 8 fails because that name does not exist yet, and WHERE rating * 2 > 8 works. Last, why: execution order is FROM, then WHERE, then SELECT, so the alias is created after WHERE has run.

Figure 3. AS names an expression in the result produced by SELECT. WHERE runs earlier, so WHERE double_rating > 8 fails. WHERE can evaluate WHERE rating * 2 > 8 because it uses the underlying expression.

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