WHERE col IN (subquery) filters rows using the list of values that the subquery returns. The key point is that instead of a fixed list IN (1, 3), you can generate a list that changes dynamically according to the state of the table.

SELECT * FROM orders
WHERE  user_id IN (
  SELECT user_id FROM users
  WHERE  plan = 'premium'   -- the inner query generates a dynamic list
);

From the orders table, retrieve the orders of users whose plan is 'premium' as order_id, user_id, amount, status, ordered_at. Sort by ordered_at in descending order.

Expected output (premium users only, ordered_at descending):

※ 103 (free) and 106 (standard) are excluded

SELECT
  order_id, user_id, amount, status, ordered_at
FROM   orders
WHERE  user_id IN (        -- IN: keep rows matching the subquery result list
  SELECT user_id            -- always return exactly one column
  FROM   users
  WHERE  plan = 'premium'  -- dynamically generate the list of premium user_ids
)
ORDER BY ordered_at DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the subquery
       → SELECT user_id FROM users WHERE plan='premium'
       → Result list: [1, 3]
  2. FROM orders              → read all rows
  3. WHERE user_id IN (1, 3)  → narrow to 4 rows
  4. SELECT ...               → select the needed columns
  5. ORDER BY ordered_at DESC → sort by date descending
*/

WHERE col NOT IN (subquery) returns the rows not contained in the subquery's result. This is the pattern for finding rows that do not exist in another table, such as "users who have never ordered" or "targets of an undelivered campaign."

SELECT * FROM users
WHERE user_id NOT IN (
  SELECT DISTINCT user_id FROM orders
  WHERE user_id IS NOT NULL   -- prevent NULL contamination (important)
);

From the users table, retrieve the users who have never placed an order. Return user_id, name, plan in ascending user_id order.

Expected output (users with no orders only):

※ DISTINCT user_id = [1,2,3,4]. Only user_id=5 is outside the list.

SELECT
  user_id, name, plan
FROM   users
WHERE  user_id NOT IN (   -- keep only rows not contained in the list
  SELECT DISTINCT user_id  -- remove duplicates with DISTINCT (efficiency)
  FROM   orders
  WHERE  user_id IS NOT NULL -- prevent NULL contamination (avoid the NOT IN trap)
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the subquery
       → SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
       → Result list: [1, 2, 3, 4]
  2. FROM users              → read all rows
  3. WHERE user_id NOT IN (1,2,3,4)
                             → only the row with user_id=5 remains
  4. SELECT user_id, name, plan  → select the needed columns
  5. ORDER BY user_id        → sort by user_id ascending
*/

When you want to apply a WHERE condition to a result aggregated with GROUP BY, use a "derived table (inline view)"—a subquery written in the FROM clause. Because a normal WHERE clause is evaluated before GROUP BY, you cannot use post-aggregation columns directly in WHERE. A derived table removes this restriction.

SELECT *
FROM (
  SELECT col, SUM(amount) AS total  -- aggregate on the inside
  FROM   table
  GROUP BY col
) AS sub                             -- always give it an alias with AS
WHERE  total >= 10000;              -- can filter by the post-aggregation value

From the orders table, aggregate the total amount per user targeting only orders whose status is 'completed', and return only the users whose total amount is 15,000 or more, in descending order of total amount. The columns to retrieve are user_id, total_amount.

Expected output (completed total ≥ 15000, total descending):

※ user_id 2 (pending) and 4 (cancelled) are excluded by the inner WHERE. The post-aggregation outer WHERE does the final filtering.

SELECT
  user_id,
  total_amount
FROM (                           -- subquery in the FROM clause (derived table)
  SELECT
    user_id,
    SUM(amount) AS total_amount  -- name the aggregate column with AS (referenced from outside)
  FROM   orders
  WHERE  status = 'completed'    -- filter in the inner query before aggregating
  GROUP BY user_id
) AS user_totals                 -- always give the derived table an AS alias
WHERE  total_amount >= 15000     -- can filter by the aggregate column name (impossible in a normal WHERE)
ORDER BY total_amount DESC;

/*
  Execution order (SQL's logical evaluation order):
  1. Evaluate the inner query (derived table)      → aggregate completed by user_id
  2. FROM ... AS user_totals       → reference the aggregated result
  3. WHERE total_amount threshold      → filter by the condition
  4. SELECT user_id, total_amount  → select columns
  5. ORDER BY total_amount DESC    → total descending
  */

WHERE EXISTS (subquery) keeps a row if the subquery "returns even one row." What it returns does not matter—it only checks whether a row exists—so SELECT 1 is enough on the inside.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1                        -- the value can be anything; only row existence is checked
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- a "correlated subquery" referencing an outer column
);

From the users table, retrieve the users who have at least one order with status 'completed'. Return user_id, name, plan in ascending user_id order.

Expected output (has completed, user_id ascending):

※ user_id 2 (pending only), 4 (cancelled only), and 5 (no orders) are excluded

SELECT
  user_id, name, plan
FROM   users u                    -- alias u for the outer query
WHERE  EXISTS (                   -- TRUE if the SQ returns one or more rows
  SELECT 1                       -- the value can be anything (1, *, 'x' are all equivalent)
  FROM   orders o
  WHERE  o.user_id = u.user_id  -- correlated SQ referencing the outer u.user_id
    AND  o.status  = 'completed' -- passes if there is at least one completed
)
ORDER BY user_id;

/*
  Execution order (SQL's logical evaluation order):
  1. FROM users u                → process one row at a time
  2. EXISTS(...)                 → run the subquery per user
  3. SELECT user_id, name, plan  → select the passing rows
  4. ORDER BY user_id            → user_id ascending
  */