The idea: a join pairs rows, and the join type decides who is left out
A join takes a row from one table and a row from another and glues them into one wider row. The ON condition says which pairs belong together. The join type says what happens to a row that finds no partner: an inner join drops it, an outer join keeps it and fills the missing side with NULL. That one rule explains almost every surprise people meet with joins.
The data on the canvas
Two small tables. emp(id, name, dept_id, boss_id) holds Ann, Bob, Cid and Dee. dept(id, name) holds Sales (10), IT (20) and HR (30). Two rows are there on purpose: Dee has dept_id NULL (no department yet), and HR has no employee. Ann has boss_id NULL because she is the top boss. Every result can be checked by hand.
To make the pairs visible, the canvas runs every join the simplest way: for each row of the outer table, compare it with every row of the other table. How a real engine finds the partners faster (index lookups, a hash table) is the topic of Nested Loop vs Hash Join vs Merge Join; the rows that come out are the same.
INNER JOIN, and why a NULL key never matches
emp e INNER JOIN dept d ON e.dept_id = d.id keeps a pair only when the condition is TRUE. Ann and Bob match Sales, Cid matches IT: 3 rows from 12 pairs. Dee's dept_id is NULL, and in SQL NULL = 10 is not FALSE but UNKNOWN; only TRUE counts, so Dee matches nothing. HR has no employee, so it is never part of a pair. Both are simply missing from the result (Demo: INNER JOIN, unmatched rows vanish). In MariaDB, JOIN, INNER JOIN and CROSS JOIN are the same thing; the word INNER is optional.
LEFT and RIGHT JOIN: keep the rows with no partner
LEFT JOIN (also written LEFT OUTER JOIN) keeps every row of the left table. A left row with partners comes out once per partner, as in an inner join. A left row with no partner comes out exactly once, and the right table's columns are padded with NULL. So the result has Ann, Bob, Cid and also Dee, NULL, NULL, NULL (Demo: LEFT JOIN keeps Dee with NULLs). HR is still missing: it is on the right side.
RIGHT JOIN is the mirror: every row of the right table is kept. MariaDB does not have a separate algorithm for it; it rewrites A RIGHT JOIN B as B LEFT JOIN A before planning, which is why the canvas loops over dept this time. HR comes out as NULL, NULL, 30, HR, and Dee is now the one missing (Demo: RIGHT JOIN keeps HR with NULLs). Most people write only LEFT JOINs and put the table they want to keep first.
A padded NULL looks the same as a stored NULL. To find "employees with no department" or "departments with no employee", test a column that can never be NULL in a real match, usually the right table's key: … LEFT JOIN dept d ON … WHERE d.id IS NULL.
CROSS JOIN and the self join
CROSS JOIN has no condition: every emp row with every dept row, 4 × 3 = 12 rows (Demo: CROSS JOIN, 4 × 3 = 12 rows). FROM emp e, dept d with no WHERE does the same. It is the Cartesian product of relational algebra; it is useful for making all combinations (every size with every colour), and a forgotten ON condition is the classic way to get it by accident.
A self join joins a table with itself. The two copies need two aliases, so each column reference says which copy it means: FROM emp e JOIN emp b ON e.boss_id = b.id reads "employee e and the row b of their boss". It gives Bob → Ann, Cid → Ann and Dee → Bob. Ann's boss_id is NULL, so this inner join drops her; a LEFT JOIN would keep her with a NULL boss (Demo: self join, each employee's boss).
FULL OUTER JOIN in MariaDB: LEFT UNION RIGHT
A FULL OUTER JOIN keeps unmatched rows from both sides. MariaDB has no FULL OUTER JOIN keyword, so it is written as the union of a LEFT JOIN and a RIGHT JOIN over the same tables. The left half gives 4 rows (the 3 matches and Dee), the right half gives 4 rows (the 3 matches and HR). The 3 matched rows appear in both halves, and UNION removes duplicate rows, so 8 rows become 5 (Demo: FULL OUTER JOIN as LEFT UNION RIGHT).
One trap: UNION also merges two real rows that happen to be equal, for example two employees with the same name in the same department. When that can happen, select a key column, or use UNION ALL and add WHERE e.id IS NULL to the second half so it only adds the right rows with no partner.
ON vs WHERE with a LEFT JOIN
For an inner join it does not matter whether a filter goes in ON or in WHERE. For an outer join it changes the answer, because the two run at different times:
- In ON, the filter is part of the matching. It decides which right rows count as partners. A left row whose partners all fail is still kept, padded with NULL.
LEFT JOIN dept d ON e.dept_id = d.id AND d.name = 'Sales'gives 4 rows: Ann and Bob with Sales, Cid and Dee with NULLs (Demo: LEFT JOIN, filter in ON keeps rows). - In WHERE, the filter runs after the join, on the finished rows. Cid's row has IT, so it fails. Dee's padded row has
d.nameNULL, andNULL = 'Sales'is UNKNOWN, so it fails too. 2 rows are left, exactly the inner join's answer (Demo: LEFT JOIN, filter in WHERE drops them).
The MariaDB optimizer knows this too. When a WHERE condition can never be TRUE on a NULL-padded row (it is null-rejecting), it turns the outer join into an inner join before choosing a plan, which also lets it pick either table first. The logical order of the clauses, FROM and its joins before WHERE, is shown on How a SELECT Runs.
Summary
| Join (MariaDB) | Rows kept | Rows here |
|---|---|---|
[INNER] JOIN … ON | pairs where ON is TRUE | 3 |
LEFT [OUTER] JOIN … ON | inner rows + left rows with no partner, padded | 4 |
RIGHT [OUTER] JOIN … ON | inner rows + right rows with no partner, padded | 4 |
CROSS JOIN | every pair | 12 |
self join emp e JOIN emp b | as inner, one table under two aliases | 3 |
LEFT … UNION RIGHT … | inner rows + both kinds of unmatched rows | 5 |
See also Relational Algebra for the join as σ over ×, Nested Loop vs Hash Join vs Merge Join for how the matching is done fast, and MariaDB: How the Optimizer Picks a Join Order for how the optimizer picks which table to read first.
See also MariaDB triggers and stored procedures, where an INSERT, UPDATE or DELETE fires code for each row it touches.
See also SQL Subqueries Row by Row for IN, EXISTS and NOT EXISTS, the semi and anti joins this page leaves out, and the NOT IN trap with NULL.
What the page leaves out
NATURAL JOIN and JOIN … USING (col), which join on columns with the same name and show them once, are only named here. So are semi joins and anti joins (IN, EXISTS, NOT EXISTS), joins of three or more tables with mixed inner and outer joins, MariaDB's STRAIGHT_JOIN, and non-equal ON conditions such as ranges. The canvas lists rows in the order its loop produces them; without ORDER BY MariaDB promises no order. MariaDB has no FULL OUTER JOIN keyword, which is why the page builds it from UNION.