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:
| Operator | Result | Since |
|---|---|---|
UNION | Rows from either query, duplicates removed | always |
UNION ALL | Rows from either query, duplicates kept (faster) | always |
INTERSECT | Rows present in both | 8.0.31 |
EXCEPT | Rows in the first but not the second | 8.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
| Need | SQL |
|---|---|
| Matching rows only | FROM a JOIN b ON b.a_id = a.id |
| Keep all left rows | FROM a LEFT JOIN b ON ... |
| Left rows without a match | LEFT JOIN b ON ... WHERE b.id IS NULL |
| Many-to-many | a JOIN a_b ON ... JOIN b ON ... |
| Full outer join | LEFT JOIN ... UNION ... RIGHT JOIN ... |
| Exists check | WHERE EXISTS (SELECT 1 FROM ... WHERE ...) |
| Top 1 per group | JOIN LATERAL (... ORDER BY ... LIMIT 1) x ON TRUE |
| Stack results | UNION ALL, INTERSECT, EXCEPT |
Exercise
Which query lists every user, including those with no notes, together with their pinned notes only?
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?
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?
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
JOINkeeps matches only;LEFT JOINkeeps every left row and fills gaps withNULL.- Put conditions on the optional table in
ON, notWHERE. - Link tables turn many-to-many relationships into two simple joins; joins multiply rows.
- Prefer
EXISTSandNOT EXISTSfor existence checks, and bewareNOT INwith NULLs. LATERALderived tables solve “top N per group”;INTERSECTandEXCEPTneed 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.