In a relational database, a relationship in which one user can have multiple tags and one tag can be assigned to multiple users is called many-to-many. Many-to-many data cannot be joined directly, so place a junction table (bridge table) between the two sides and retrieve the data with two consecutive JOINs.

FROM   users AS u
INNER JOIN user_tags AS ut ON u.user_id  = ut.user_id   -- first JOIN (to the junction table)
INNER JOIN tags      AS t  ON ut.tag_id = t.tag_id; -- second JOIN (to the destination table)

There are user information (users), tag information (tags), and a junction table (user_tags) that manages their associations.

Join these three tables and retrieve a list of user names (name) and tag names (tag_name).

SELECT
  u.name,
  t.tag_name

FROM users AS u
INNER JOIN user_tags AS ut   -- ① first join the junction table to find each user's tag_id
  ON u.user_id = ut.user_id
INNER JOIN tags AS t         -- ② use the junction table's tag_id to find the tag name in tags
  ON ut.tag_id = t.tag_id;

/*
  Execution order:
  1. FROM users AS u           → read users (2 rows)
  2. INNER JOIN user_tags AS ut→ match by user_id. Tanaka (1) expands to 2 rows and Sato (2) to 1 row (3-row intermediate result)
  3. INNER JOIN tags AS t      → match the 3 expanded rows with tags by tag_id
  4. SELECT u.name, t.tag_name → project only the required text columns and output them
*/

A JOIN's ON clause can use non-equality conditions such as BETWEEN and >=, not only = (equality). This is called a non-equi JOIN.

It is a powerful technique when classifying sales amounts into ranks (A–C), checking whether a date falls within a campaign period, or joining to a range master in another table.

FROM   scores AS s
INNER JOIN scales AS g
  -- join rows where score falls between min and max
  ON s.score BETWEEN g.min_score AND g.max_score;

There are student test scores (test_scores) and a master table of grading criteria (grading_scales). Join them and retrieve the student (student), score (score), and grade rank (grade).

SELECT
  ts.student,
  ts.score,
  gs.grade

FROM test_scores AS ts
INNER JOIN grading_scales AS gs                       -- join the master row whose range contains the score, not an equal value
  ON ts.score BETWEEN gs.min_score AND gs.max_score;

/*
  Execution order:
  1. FROM test_scores AS ts       → read the score table (3 rows)
  2. INNER JOIN grading_scales    → check whether each ts.score falls within a gs min–max range
                                  Tanaka's 85 matches 80–100 (A)
                                  Sato's 55 matches 0–59 (C)
                                  Yamada's 72 matches 60–79 (B)
  3. SELECT ts.student, ...       → project 3 columns from the joined virtual table
*/

FULL OUTER JOIN combines the characteristics of both LEFT JOIN and RIGHT JOIN.
It keeps every row from both the left and right tables in the result, and fills the columns on the missing side with NULL whenever the data does not match.

FROM   table_a AS a
FULL OUTER JOIN table_b AS b
  ON a.id = b.id;

There are a department master table (departments) and an employee table (employees).

Join them on dept_id and output both departments with no employees and employees whose department has not been assigned.

SELECT
  d.dept_name,
  e.name

FROM departments AS d
FULL OUTER JOIN employees AS e  -- keep both the HR department and Suzuki with no department
  ON d.dept_id = e.dept_id;

/*
  Execution order:
  1. FROM departments AS d        → read the department table (3 rows)
  2. FULL OUTER JOIN employees e  → match both sides by dept_id
       ・matched: Sales (Tanaka), Development (Sato)
       ・left side only: HR (the e side is NULL-padded)
       ・right side only: Suzuki (the d side is NULL-padded)
  3. SELECT d.dept_name, e.name   → project all rows (4 total) and output them
*/

The table you join does not have to be a physical table. You can first aggregate data with GROUP BY inside (), define the result as a derived table (inline view), and JOIN it to the main table.

FROM   users AS u
LEFT JOIN (
  -- aggregate totals or other values by user in advance
  SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
) AS o
  ON u.user_id = o.user_id;

There are a user table (users) and an order history table (orders).

First use a subquery to create a virtual table containing each user's latest order date (last_order), then LEFT JOIN it to users and output the latest order date for every user. Users with no orders should have NULL.

SELECT
  u.name,
  lo.last_order

FROM users AS u
LEFT JOIN (
  -- virtual table: aggregate orders by user and obtain the maximum date
  SELECT
    user_id,
    MAX(order_date) AS last_order
  FROM orders
  GROUP BY user_id
) AS lo
  ON u.user_id = lo.user_id;       -- join users to the virtual table (lo) by user_id

/*
  Execution order:
  1. Subquery FROM orders    → read orders
  2. Subquery aggregation    → calculate MAX date per user (lo)
  3. FROM users AS u          → read users
  4. LEFT JOIN ... AS lo      → join lo (unmatched users become NULL)
  5. SELECT u.name, ...       → project the columns
  */