With LEFT JOIN, placing a filter in the ON clause or the WHERE clause produces different results. This is one of the most common join bugs in production queries.

-- ✓ ON filter: preserve every user and join only shipped orders
LEFT JOIN orders AS o
  ON  u.user_id = o.user_id
  AND o.status  = 'shipped'  -- part of matching; all left rows remain

-- ✗ WHERE filter: removes NULL-padded rows and behaves like INNER JOIN
LEFT JOIN orders AS o ON u.user_id = o.user_id
WHERE o.status = 'shipped'  -- NULL fails the comparison, so users without matches disappear

Retrieve every row from users together with that user's orders whose status = 'shipped'. Show NULL for users with no shipped orders or no orders at all. Implement the condition by adding AND o.status = 'shipped' to the ON clause.

Alice has both a shipped order (id 1) and a pending order (id 2). The pending order fails the ON condition and is not joined.

SELECT
  u.name,
  o.order_id,
  o.status

FROM users AS u
LEFT JOIN orders AS o
  ON  u.user_id = o.user_id   -- join key
  AND o.status  = 'shipped';  -- join only shipped orders while retaining every user

-- Moving AND to WHERE is incorrect here:
-- WHERE o.status = 'shipped' also removes Carol's NULL-padded row,
-- making the result equivalent to an INNER JOIN.

/*
  Execution order:
  1. FROM users AS u                      → read the left table
  2. LEFT JOIN orders AS o                → match user_id and status = 'shipped'
  3. SELECT u.name, o.order_id, o.status  → project the requested columns
*/

If an API returns NULL columns produced by LEFT JOIN, every client must perform its own null checks. COALESCE lets the SQL query replace those NULLs with suitable default values.

COALESCE(expr, default_value)
-- Return default_value when expr is NULL; otherwise return expr.
-- More arguments are allowed: COALESCE(a, b, c) returns the first non-NULL value.

SELECT
  u.name,
  COALESCE(pr.bio, 'Not set')          AS bio,     -- NULL → 'Not set'
  COALESCE(pr.avatar_url, '/default.png') AS avatar  -- NULL → default image
FROM users AS u
LEFT JOIN profiles AS pr ON u.user_id = pr.user_id;

Retrieve every user and their profile fields bio and avatar_url. For a user with no profile, display 'Not set' for bio and '/img/default.png' for avatar_url.

SELECT
  u.name,
  COALESCE(pr.bio,        'Not set')           AS bio,           -- default when no profile bio exists
  COALESCE(pr.avatar_url, '/img/default.png') AS avatar_url  -- default when no avatar exists

FROM users AS u
LEFT JOIN profiles AS pr   -- preserve users without profiles
  ON u.user_id = pr.user_id;

/*
  Execution order:
  1. FROM users AS u           → read all three users
  2. LEFT JOIN profiles AS pr  → match profile rows by user_id
  3. SELECT + COALESCE(...)    → replace NULL profile values
*/

HAVING filters aggregate results after GROUP BY. WHERE filters individual rows before aggregation, so aggregate functions such as COUNT or SUM cannot be used directly in WHERE.

SELECT   u.name, COUNT(o.order_id) AS order_count
FROM     users AS u
INNER JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
HAVING   COUNT(o.order_id) >= 2;  -- filter after aggregation

Join users and orders, then retrieve the name and order count for users who have at least two orders. Sort by order count descending.

SELECT
  u.name,
  COUNT(o.order_id)  AS order_count  -- count orders in each user group

FROM users AS u
INNER JOIN orders AS o
  ON u.user_id = o.user_id

GROUP BY u.user_id, u.name  -- include every non-aggregate SELECT column

HAVING COUNT(o.order_id) >= 2  -- filter aggregate values after GROUP BY

ORDER BY order_count DESC;  -- sort the aggregate result descending

/*
  Execution order:
  1. FROM users AS u           → read three users
  2. INNER JOIN orders AS o    → create a five-row joined table
  3. WHERE                     → none
  4. GROUP BY u.user_id        → form three groups: Alice 2, Bob 1, Carol 2
  5. HAVING COUNT >= 2         → remove Bob's one-row group
  6. SELECT u.name, COUNT(...) → project the two remaining groups
  7. ORDER BY order_count DESC → sort; tied rows have database-dependent order
*/

CROSS JOIN has no ON clause. It generates every combination of every left-table row with every right-table row. The result contains “left row count × right row count” rows.

FROM   colors AS c
CROSS JOIN sizes AS s;
-- No ON clause: 3 colors × 3 sizes = 9 rows.

Generate every product variant formed from color and size. CROSS JOIN the three rows in colors with the three rows in sizes, and return all nine color_name and size_name combinations.

SELECT
  c.color_name,
  s.size_name

FROM colors AS c
CROSS JOIN sizes AS s  -- no ON clause: 3 × 3 produces 9 combinations

ORDER BY c.color_id, s.size_id; -- order by color, then size

/*
  Execution order:
  1. FROM colors AS c         → read three colors
  2. CROSS JOIN sizes AS s    → combine each color with all three sizes
                                 Red   × (S, M, L) → 3 rows
                                 Blue  × (S, M, L) → 3 rows
                                 Green × (S, M, L) → 3 rows
                                 total: 3 × 3 = 9 rows
  3. SELECT c.color_name, s.size_name → project two columns
  4. ORDER BY c.color_id, s.size_id   → sort by color and size IDs
*/