Lesson 08 intermediate

Joins, Subqueries and Set Operations

Combine tables the way Orbit's API needs: INNER JOIN, LEFT and RIGHT JOIN, finding rows with no match, many-to-many joins through a link table, self joins and CROSS JOIN, emulating FULL OUTER JOIN, scalar, IN and EXISTS subqueries, correlated subqueries, derived tables and LATERAL, and UNION, INTERSECT and EXCEPT.

Orbit spreads its data over four tables so that each fact is stored once. Joins put the pieces back together. This lesson covers every join MySQL supports, subqueries and the set operators.

INNER JOIN

An inner join returns the combinations of rows that satisfy the ON condition:

SELECT n.id, n.title, u.name
FROM notes n
JOIN users u ON u.id = n.user_id
ORDER BY n.id;
+----+------------------+------------+
| id | title            | name       |
+----+------------------+------------+
|  1 | Shopping list    | Ana Silva  |
|  2 | Deploy checklist | Ana Silva  |
|  3 | Book ideas       | Ben Carter |
|  4 | MySQL tips       | Ben Carter |
|  5 | Meeting notes    | Ana Silva  |
+----+------------------+------------+
5 rows in set (0.00 sec)

JOIN means INNER JOIN. The short names n and u are table aliases; once you give one, use it everywhere in the query. When both columns have the same name you can write USING (col) instead of ON a.col = b.col.

Chen has no notes, so Chen does not appear: an inner join only keeps rows that found a partner.

LEFT JOIN

A left join keeps every row from the left table. Where no right row matches, the right columns are NULL:

SELECT u.name, n.title
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
ORDER BY u.id, n.id;
+------------+------------------+
| name       | title            |
+------------+------------------+
| Ana Silva  | Shopping list    |
| Ana Silva  | Deploy checklist |
| Ana Silva  | Meeting notes    |
| Ben Carter | Book ideas       |
| Ben Carter | MySQL tips       |
| Chen Wei   | NULL             |
+------------+------------------+
6 rows in set (0.00 sec)

That makes “rows with no match” easy to find. Which users have never written a note?

SELECT u.id, u.name
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
WHERE n.id IS NULL;
+----+----------+
| id | name     |
+----+----------+
|  3 | Chen Wei |
+----+----------+
1 row in set (0.00 sec)

Warning: A condition on the right table belongs in ON, not WHERE. LEFT JOIN notes n ON n.user_id = u.id WHERE n.pinned throws away users without notes again, because n.pinned is NULL for them. Write LEFT JOIN notes n ON n.user_id = u.id AND n.pinned to keep every user and only attach pinned notes.

RIGHT JOIN is the mirror image: it keeps every row of the right table. Any right join can be written as a left join by swapping the tables, which most people find easier to read.

Many-to-many through a link table

Notes and tags are connected by note_tags. Two joins walk from one side to the other:

SELECT n.title, t.name AS tag
FROM notes n
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
ORDER BY n.id, t.name;
+------------------+-----------+
| title            | tag       |
+------------------+-----------+
| Shopping list    | personal  |
| Deploy checklist | work      |
| Book ideas       | personal  |
| MySQL tips       | databases |
| MySQL tips       | work      |
| Meeting notes    | work      |
+------------------+-----------+
6 rows in set (0.00 sec)

“MySQL tips” appears twice because it has two tags. Joins multiply rows: a note with three tags joined to a user gives three rows. Lesson 9 shows how GROUP_CONCAT folds them back into one row per note.

Self joins

A table can be joined to itself with two different aliases. Which pairs of users share a plan?

SELECT a.name AS user_a, b.name AS user_b, a.plan
FROM users a
JOIN users b ON b.plan = a.plan AND b.id > a.id;
+------------+----------+------+
| user_a     | user_b   | plan |
+------------+----------+------+
| Ben Carter | Chen Wei | pro  |
+------------+----------+------+
1 row in set (0.00 sec)

b.id > a.id keeps each pair once and stops a user being paired with themselves. Self joins are also how you read hierarchies stored as a parent_id column, such as folders or org charts; lesson 10 shows the recursive version.

CROSS JOIN

A cross join pairs every row with every row. Three users times three tags gives nine rows, which is useful for building a grid such as “every user, every tag, with a count that may be zero”:

SELECT COUNT(*) FROM users CROSS JOIN tags;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

Forgetting the ON clause on a normal join produces the same explosion by accident, so treat an unexpectedly huge result as a hint.

FULL OUTER JOIN

MySQL has no FULL OUTER JOIN. When you need rows from both sides whether or not they match, combine a left and a right join with UNION:

SELECT u.name, n.title FROM users u LEFT JOIN notes n ON n.user_id = u.id
UNION
SELECT u.name, n.title FROM users u RIGHT JOIN notes n ON n.user_id = u.id;

In Orbit every note has a user (the foreign key guarantees it), so this returns the same rows as the left join. It matters when neither side is guaranteed, such as comparing two imported lists.

Subqueries

A subquery is a SELECT inside another statement.

Scalar subquery, one value. Notes longer than the average body:

SELECT id, title FROM notes
WHERE CHAR_LENGTH(body) > (SELECT AVG(CHAR_LENGTH(body)) FROM notes)
ORDER BY id;
+----+------------------+
| id | title            |
+----+------------------+
|  2 | Deploy checklist |
|  4 | MySQL tips       |
+----+------------------+
2 rows in set (0.00 sec)

IN subquery, a list of values. Users with at least one pinned note:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM notes WHERE pinned)
ORDER BY id;
+------------+
| name       |
+------------+
| Ana Silva  |
| Ben Carter |
+------------+
2 rows in set (0.00 sec)

EXISTS, true when the subquery returns any row. Users with a note tagged “work”:

SELECT u.name FROM users u
WHERE EXISTS (
  SELECT 1
  FROM notes n
  JOIN note_tags nt ON nt.note_id = n.id
  JOIN tags t ON t.id = nt.tag_id
  WHERE n.user_id = u.id AND t.name = 'work'
)
ORDER BY u.id;
+------------+
| name       |
+------------+
| Ana Silva  |
| Ben Carter |
+------------+
2 rows in set (0.00 sec)

This subquery is correlated: it refers to u.id from the outer query, so conceptually it runs once per user. MySQL’s optimizer usually turns IN and EXISTS into efficient semi-joins, so pick whichever reads more clearly.

Warning: NOT IN (subquery) returns no rows at all if the subquery produces a single NULL, because x <> NULL is unknown. Prefer NOT EXISTS or a LEFT JOIN ... IS NULL for “not in” logic.

Derived tables and LATERAL

A subquery in FROM is a derived table; it must have an alias. Count notes per user first, then filter the counts:

SELECT u.name, c.notes
FROM users u
JOIN (SELECT user_id, COUNT(*) AS notes FROM notes GROUP BY user_id) AS c
  ON c.user_id = u.id
WHERE c.notes >= 2
ORDER BY u.id;
+------------+-------+
| name       | notes |
+------------+-------+
| Ana Silva  |     3 |
| Ben Carter |     2 |
+------------+-------+
2 rows in set (0.00 sec)

A plain derived table cannot refer to tables earlier in the FROM clause. Add LATERAL (MySQL 8.0.14+) and it can, which answers “the latest note of each user” neatly:

SELECT u.name, latest.title
FROM users u
JOIN LATERAL (
  SELECT title FROM notes n
  WHERE n.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) AS latest ON TRUE
ORDER BY u.id;
+------------+---------------+
| name       | title         |
+------------+---------------+
| Ana Silva  | Meeting notes |
| Ben Carter | MySQL tips    |
+------------+---------------+
2 rows in set (0.00 sec)

Use LEFT JOIN LATERAL to keep users who have no notes.

UNION, INTERSECT and EXCEPT

Set operators stack the results of two queries with the same number of columns:

OperatorResultSince
UNIONRows from either query, duplicates removedalways
UNION ALLRows from either query, duplicates kept (faster)always
INTERSECTRows present in both8.0.31
EXCEPTRows in the first but not the second8.0.31

Users who have a pinned note and a note tagged “work”:

SELECT user_id FROM notes WHERE pinned
INTERSECT
SELECT n.user_id FROM notes n JOIN note_tags nt ON nt.note_id = n.id WHERE nt.tag_id = 1;
+---------+
| user_id |
+---------+
|       1 |
|       2 |
+---------+
2 rows in set (0.00 sec)

Ana pinned the shopping list and tagged other notes “work”; Ben’s “MySQL tips” is both. Column names come from the first query, and ORDER BY at the end sorts the combined result. Use UNION ALL unless you really need duplicates removed: the deduplication costs a sort or hash of the whole result.

Quick reference

NeedSQL
Matching rows onlyFROM a JOIN b ON b.a_id = a.id
Keep all left rowsFROM a LEFT JOIN b ON ...
Left rows without a matchLEFT JOIN b ON ... WHERE b.id IS NULL
Many-to-manya JOIN a_b ON ... JOIN b ON ...
Full outer joinLEFT JOIN ... UNION ... RIGHT JOIN ...
Exists checkWHERE EXISTS (SELECT 1 FROM ... WHERE ...)
Top 1 per groupJOIN LATERAL (... ORDER BY ... LIMIT 1) x ON TRUE
Stack resultsUNION ALL, INTERSECT, EXCEPT

Exercise

Which query lists every user, including those with no notes, together with their pinned notes only?

  1. FROM users u LEFT JOIN notes n ON n.user_id = u.id AND n.pinned
  2. FROM users u LEFT JOIN notes n ON n.user_id = u.id WHERE n.pinned
  3. FROM users u JOIN notes n ON n.user_id = u.id AND n.pinned
  4. FROM notes n LEFT JOIN users u ON u.id = n.user_id WHERE n.pinned
Show answer

Answer: A. Filtering the right table in WHERE removes the NULL rows a left join creates. Putting the condition in ON keeps every user and only attaches matching notes.

Exercise

Why can NOT IN (SELECT user_id FROM notes) return nothing even when some users have no notes?

  1. NOT IN only works with literal lists
  2. MySQL rewrites it as an inner join
  3. If the subquery returns a NULL, every comparison becomes unknown
  4. NOT IN ignores rows with NULL ids
Show answer

Answer: C. x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the second part is never true. NOT EXISTS does not have this trap.

Exercise

How do you get a FULL OUTER JOIN in MySQL?

  1. Use FULL JOIN, which is a synonym
  2. Use CROSS JOIN with a WHERE clause
  3. It is impossible in MySQL
  4. Combine a LEFT JOIN and a RIGHT JOIN with UNION
Show answer

Answer: D. MySQL does not implement FULL OUTER JOIN. The UNION of a left and a right join returns matched rows plus unmatched rows from both sides.

Key takeaways

  • JOIN keeps matches only; LEFT JOIN keeps every left row and fills gaps with NULL.
  • Put conditions on the optional table in ON, not WHERE.
  • Link tables turn many-to-many relationships into two simple joins; joins multiply rows.
  • Prefer EXISTS and NOT EXISTS for existence checks, and beware NOT IN with NULLs.
  • LATERAL derived tables solve “top N per group”; INTERSECT and EXCEPT need MySQL 8.0.31+.

FAQ

Are joins slow?

Joins on indexed columns are what relational databases are built for. Orbit’s foreign key columns are indexed, so joining notes to users is cheap. Slow joins almost always mean a missing index on the join column; lesson 13 shows how to spot it with EXPLAIN.

Is a subquery slower than a join?

Not in modern MySQL. The optimizer rewrites many subqueries into joins or semi-joins. Write the version that expresses your intent most clearly, then check EXPLAIN if it turns out slow.

What is the difference between ON and USING?

USING (user_id) is shorthand for ON a.user_id = b.user_id when both columns have the same name, and the column appears once in SELECT *. Orbit’s columns are named notes.user_id and users.id, so it needs ON.

Should I use NATURAL JOIN?

No. It joins on every column with the same name, so adding a created_at column to both tables silently changes the join. Always spell out the join condition.

↑ ↓ navigate Enter open Esc close