Combining tables in queries.
WHAT A JOIN DOES
Matches rows from two tables on a condition.
WHAT AN INNER JOIN RETURNS
Only rows matching in both.
WHAT A LEFT JOIN RETURNS
Every row from the left table, with matches where they exist and empty values where they do not.
WHY THAT MATTERS
It answers questions like which customers placed no orders.
WHAT A RIGHT JOIN IS
The same, reversed; rarely used, since the tables can be swapped.
WHAT A FULL OUTER JOIN RETURNS
Everything from both sides.
WHAT MYSQL LACKS
A full outer join, which must be built from two joins combined.
WHAT A CROSS JOIN PRODUCES
Every combination.
WHY THAT IS USUALLY AN ACCIDENT
Omitting the join condition produces one, and the result explodes.
WHAT THE SYMPTOM IS
A query returning vastly more rows than expected, running for a long time.
WHAT TO INDEX FOR JOINS
The columns being matched, on both sides.
WHY BOTH
The optimiser may choose either as the driving table.
WHAT TO BE CAREFUL WITH
Joining on columns of different types Joining on columns with different collations
WHY
Both prevent index use silently.
WHAT TO WATCH IN THE PLAN
Whether each table uses an index.
WHAT TO AVOID
Joining many tables when a smaller query would do.
