Unlike INNER JOIN, a LEFT JOIN (left outer join) always keeps every row of the left table (the FROM side). When no row of the right table satisfies the ON condition, all of the right table's columns are output as NULL.

SELECT columns, ...
FROM   left_table AS u   -- every row is always output
LEFT JOIN right_table AS o
  ON u.key = o.key;       -- if nothing matches, o.* is NULL

Retrieve every user in the users table together with their order information (order_id, amount). Users with no orders at all should show NULL for order_id and amount.

Note: Yamada (user_id = 3) has no orders, and Tanaka (user_id = 1) has two.

SELECT
  u.user_id,
  u.name,
  o.order_id,  -- Yamada (user_id = 3) matches nothing in orders, so these columns become NULL
  o.amount

FROM users AS u            -- keep every user (the left table)
LEFT JOIN orders AS o    -- join only the matching orders rows; NULL-pad when none
  ON u.user_id = o.user_id;

/*
  Execution order:
  1. FROM users AS u        → read the left table
  2. LEFT JOIN orders AS o  → match on ON (NULL-pad the unmatched)
  3. SELECT ...             → project the columns and output
  */

Applying the LEFT JOIN property from Q2 (unmatched right-side rows become NULL), you can retrieve "records that exist in the left table but not in the right" — in other words, a set difference.

FROM   left_table AS u
LEFT JOIN right_table AS o ON u.key = o.key
WHERE  o.key IS NULL  -- keep only NULL rows = the rows that matched nothing on the right

From the users table, retrieve the users (user_id and name) who have no orders at all in the orders table.

SELECT
  u.user_id,
  u.name

FROM users AS u
LEFT JOIN orders AS o       -- first, LEFT JOIN every user against orders
  ON u.user_id = o.user_id  -- users matching no order get NULL in every o.* column
WHERE o.order_id IS NULL;   -- keep only the NULL rows = users who never ordered

/*
  Execution order:
  1. FROM users AS u           → read users
  2. LEFT JOIN orders AS o     → match on ON, NULL-pad the rest
  3. WHERE o.order_id IS NULL  → only the unmatched rows pass
  4. SELECT u.user_id, u.name  → project 2 columns
  */

To join three or more tables, chain the JOINs. The second JOIN applies to the result of the first (a virtual table).

FROM   order_items AS oi
INNER JOIN orders  AS o  ON oi.order_id  = o.order_id  -- ① first join
INNER JOIN users   AS u  ON o.user_id   = u.user_id;  -- ② joined onto the virtual table from ①

From the order line items (order_items) of an e-commerce site, retrieve the ordering user's name (user_name), the product name (product_name) and the quantity (qty).

Join with INNER JOIN in the order order_items → orders → users.

SELECT
  u.name        AS user_name,  -- the name column of the users table
  oi.product_name,             -- the product name from order_items
  oi.qty                       -- the quantity from order_items

FROM order_items AS oi         -- start from the line items
INNER JOIN orders AS o         -- ① join line items to orders on order_id
  ON oi.order_id = o.order_id
INNER JOIN users  AS u         -- ② join users onto the virtual table from ① via user_id
  ON o.user_id   = u.user_id;

/*
  Execution order:
  1. FROM order_items AS oi      → read the 3 line-item rows
  2. INNER JOIN orders AS o      → match oi.order_id with o.order_id and join (3 rows kept)
     intermediate table: order_items + orders merge, adding the user_id column
  3. INNER JOIN users AS u       → match o.user_id with u.user_id and join (3 rows kept)
     intermediate table: u.name is added as well (all tables' data now present)
  4. SELECT u.name, oi.product_name, oi.qty  → project the 3 columns we need
*/

Combining JOIN with GROUP BY lets you write aggregate queries that span multiple tables. SQL is evaluated in the order FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Because the JOIN builds a virtual table before GROUP BY aggregates it, columns from different tables can freely serve as grouping keys or aggregation targets.

FROM   users AS u
INNER JOIN orders AS o ON u.user_id = o.user_id
-- aggregate the joined virtual table with GROUP BY
GROUP BY u.user_id, u.name

Join the users and orders tables and aggregate, per user, the number of orders (order_count) and the total amount (total_amount). Sort the output by total amount, largest first (descending).

SELECT
  u.name,
  COUNT(o.order_id)  AS order_count,  -- counts non-NULL values (o.order_id always exists for a real order)
  SUM(o.amount)      AS total_amount  -- total of the order amounts

FROM users AS u
INNER JOIN orders AS o  -- users with no orders are dropped here (because it is an INNER JOIN)
  ON u.user_id = o.user_id
GROUP BY u.user_id, u.name  -- list every non-aggregated SELECT column (u.name) in GROUP BY
ORDER BY total_amount DESC; -- sort by the aggregated column's alias

/*
  Execution order:
  1. FROM users AS u            → read the 3 users rows
  2. INNER JOIN orders AS o     → join with orders (a 5-row virtual table)
  3. GROUP BY u.user_id         → split into 3 groups, one per user
  4. COUNT / SUM                → aggregate order count and total per group
  5. SELECT u.name, ...         → project the aggregated columns
  6. ORDER BY total_amount DESC → sort by total amount, descending
*/