Joins combine rows from two or more tables based on a related column, most often a foreign key. This is where relational databases really earn the "relational" in their name.
Returns only rows that have a match in both tables.
SELECT columns
FROM tableA
INNER JOIN tableB ON condition;SELECT e.name, d.department_name
FROM Employee e
INNER JOIN Department d
ON e.department_id = d.id;Common Mistake
Joining on the wrong column and silently multiplying rows.
Returns every row from the left table, matched rows from the right.
SELECT columns
FROM tableA
LEFT JOIN tableB ON condition;SELECT e.name, d.department_name
FROM Employee e
LEFT JOIN Department d
ON e.department_id = d.id;Common Mistake
Expecting a LEFT JOIN to exclude unmatched left-side rows — it never does, that's the entire point of a LEFT JOIN.
Returns every row from the right table, matched rows from the left.
SELECT columns
FROM tableA
RIGHT JOIN tableB ON condition;SELECT e.name, d.department_name
FROM Employee e
RIGHT JOIN Department d
ON e.department_id = d.id;Common Mistake
Using RIGHT JOIN out of habit when a LEFT JOIN with the table order swapped would read more clearly.
Returns all rows from both tables, matched or not.
SELECT columns
FROM tableA
FULL JOIN tableB ON condition;SELECT e.name, d.department_name
FROM Employee e
FULL JOIN Department d
ON e.department_id = d.id;Common Mistake
Not every database engine supports FULL JOIN natively — MySQL does not, for example, and needs a workaround.
| Join type | Returns |
|---|---|
| INNER JOIN | Only rows that match in both tables |
| LEFT JOIN | Every left-table row, matched right-table data or NULL |
| RIGHT JOIN | Every right-table row, matched left-table data or NULL |
| FULL JOIN | Every row from both tables, matched or not |
Joins let you pull related data together from separate tables. The next lesson moves from combining tables to summarizing values within them, with SQL's built-in functions.