When you connect several tables to a base table, you can accidentally attach an INNER JOIN after a LEFT JOIN that deliberately allowed NULL rows. The rows you took care to preserve then disappear.

-- ✗ Wrong chain (users without orders disappear)
FROM users LEFT JOIN orders ON ... INNER JOIN items ON ...

-- ✓ Correct chain (connect everything with LEFT JOIN)
FROM users LEFT JOIN orders ON ... LEFT JOIN items ON ...

There are three tables: users (users), orders (orders), and an item master (items).

Write the correct SQL to output every user's name, including users with no order history, together with the name of the item they ordered. When there is no order, the item name should be NULL.

SELECT
  u.name,
  i.item_name

FROM users AS u
LEFT JOIN orders AS o        -- keep every user while joining orders (Suzuki becomes NULL)
  ON u.user_id = o.user_id
LEFT JOIN items AS i        -- LEFT JOIN the items too (INNER would remove Suzuki)
  ON o.item_id = i.item_id;

/*
  Execution order:
  1. FROM users AS u
  2. LEFT JOIN orders AS o
  3. LEFT JOIN items AS i        → join items to the virtual table
  4. SELECT u.name, i.item_name  → project the requested columns
  */

It is common to retrieve every column from one specific row (Top-1) that has the latest date or highest score within each group, such as each user.
Because GROUP BY alone cannot retrieve columns other than the grouping key and aggregates, calculate the latest date in a subquery and INNER JOIN it back to the original table on a composite key to retrieve the complete record.

FROM logs AS a
INNER JOIN (
  SELECT id, MAX(date) AS max_d FROM logs GROUP BY id
) AS b
  ON a.id = b.id AND a.date = b.max_d;

There is a user login history table (login_history). The same user may log in multiple times.

Extract only each user's latest login record and retrieve user_id, the latest date (login_date), and the device used at that time (device).

SELECT
  h.user_id,
  h.login_date,
  h.device

FROM login_history AS h
INNER JOIN (
  -- subquery: calculate the latest date for each user
  SELECT user_id, MAX(login_date) AS max_date
  FROM login_history
  GROUP BY user_id
) AS latest
  ON  h.user_id = latest.user_id               -- join and extract only rows matching both user ID and date (= the latest row)
  AND h.login_date = latest.max_date;

/*
  Execution order:
  1. [Subquery] FROM login_history       → the table
  2. [Subquery] GROUP BY user_id         → calculate MAX date for each user
  3. FROM login_history AS h             → the main table
  4. INNER JOIN ... AS latest ON ...     → match the main rows with the subquery result on the composite key
  5. SELECT h.user_id, ...               → project the required columns
  */

In a meeting-room booking system, combine a self-join with a non-equi join to detect whether two different bookings overlap in time.

The logical condition for two periods (Start–End) to overlap can be expressed in one line:
A.start < B.end AND A.end > B.start
Write this in the ON clause and JOIN the table to itself to extract only the overlapping pairs.

There is a meeting-room reservation table (reservations).

Find pairs of reservations whose times overlap (double bookings) in the same room, and output both reservation IDs (res_a, res_b) and the room name.

To avoid comparing a reservation with itself and avoid duplicate reverse-order pairs such as (A,B) and (B,A), also add the condition r1.res_id < r2.res_id.

SELECT
  r1.res_id AS res_a,
  r2.res_id AS res_b,
  r1.room_name

FROM reservations AS r1
INNER JOIN reservations AS r2      -- join the table to itself (SELF JOIN)
  ON  r1.room_name = r2.room_name
  AND r1.res_id < r2.res_id        -- eliminate identical and reverse-order pairs
  AND r1.start_t < r2.end_t        -- logic for determining whether the periods overlap
  AND r1.end_t   > r2.start_t;

/*
  Execution order:
  1. FROM reservations AS r1        → read 4 rows as the base
  2. INNER JOIN reservations AS r2  → generate combinations and evaluate them in ON
  3. SELECT r1.res_id, ...          → project and output the one surviving pair
  */

For time-series data, when you need to compare across rows — such as yesterday's sales or the previous score — SQL can place the same table twice and shift a date or ID by -1 in the join condition.

FROM daily_kpi AS t1
-- join so that t2 becomes the record immediately before t1
LEFT JOIN daily_kpi AS t2
  ON t1.day_id - 1 = t2.day_id;

There is a table (daily_kpi) recording daily access counts. For clarity, it uses a sequential day_id rather than dates.

Self-join this table to place today's (today_users) and yesterday's (yesterday_users) access counts side by side, then calculate and output their difference (diff).
For a day with no previous day, such as day 1, yesterday_users and diff should be NULL.

SELECT
  t1.day_id,
  t1.users AS today_users,
  t2.users AS yesterday_users,
  t1.users - t2.users AS diff

FROM daily_kpi AS t1
LEFT JOIN daily_kpi AS t2        -- keep today's row even when the previous day's data is missing
  ON t1.day_id - 1 = t2.day_id;  -- match t1's ID minus 1 to t2's yesterday ID

/*
  Execution order:
  1. FROM daily_kpi AS t1       → read 3 rows as t1 (the today axis)
  2. LEFT JOIN daily_kpi AS t2  → match each t1 row with the day_id reduced by 1
  3. SELECT ...                 → place the matched rows side by side, calculate diff, and output
  */