A join combines rows from two tables using a column they share. The join type decides one thing only: what happens to rows that have no partner in the other table. INNER JOIN drops them, LEFT JOIN keeps the unmatched rows of the left table, RIGHT JOIN keeps those of the right table, and a full outer join keeps both. MySQL supports the first three directly; the fourth needs a small workaround, shown below.
Two small tables with deliberate gaps
CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(15));
CREATE TABLE enrolments (enrol_id INT PRIMARY KEY, student_id INT, course VARCHAR(20));
INSERT INTO students VALUES
(1,'Aman','Patna'), (2,'Bhavna','Ranchi'), (3,'Chirag','Surat'), (4,'Divya','Mysuru');
INSERT INTO enrolments VALUES
(101,1,'SQL'), (102,1,'Power BI'), (103,2,'Python'), (104,3,'SQL'), (105,7,'Tableau');
Divya has not enrolled in anything. Enrolment 105 belongs to student 7, who is not in the students table (perhaps the record was deleted, and there is no foreign key to stop it). These two gaps are what make the join types behave differently.
INNER JOIN: only matching pairs
SELECT s.name, e.course
FROM students s
INNER JOIN enrolments e ON e.student_id = s.student_id
ORDER BY e.enrol_id;
| name | course |
|---|---|
| Aman | SQL |
| Aman | Power BI |
| Bhavna | Python |
| Chirag | SQL |
Four rows. Divya is missing (no enrolment) and so is the Tableau enrolment (no student). Aman appears twice because he has two matching enrolments: a join returns one row per matching pair, not one per student. JOIN on its own means INNER JOIN.
LEFT JOIN: every row from the left table
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
ORDER BY s.student_id, e.enrol_id;
| name | course |
|---|---|
| Aman | SQL |
| Aman | Power BI |
| Bhavna | Python |
| Chirag | SQL |
| Divya | NULL |
Divya is back, with NULL in the columns that would have come from enrolments. That NULL makes a very useful pattern possible, the anti-join: "students who have not enrolled".
SELECT s.name
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.enrol_id IS NULL;
| name |
|---|
| Divya |
Test the IS NULL on a column that can never be NULL in a real match, such as the right table's primary key.
RIGHT JOIN: every row from the right table
SELECT e.enrol_id, e.student_id, s.name, e.course
FROM students s
RIGHT JOIN enrolments e ON e.student_id = s.student_id
ORDER BY e.enrol_id;
| enrol_id | student_id | name | course |
|---|---|---|---|
| 101 | 1 | Aman | SQL |
| 102 | 1 | Aman | Power BI |
| 103 | 2 | Bhavna | Python |
| 104 | 3 | Chirag | SQL |
| 105 | 7 | NULL | Tableau |
Now the orphan enrolment 105 is kept and Divya is not. A right join is just a left join with the tables swapped: FROM enrolments e LEFT JOIN students s ... returns the same rows. Most teams write only left joins because a query reads more naturally when the "main" table comes first. A side-by-side comparison is in LEFT JOIN vs RIGHT JOIN.
FULL OUTER JOIN: not supported, so emulate it
Writing FULL OUTER JOIN in MySQL gives ERROR 1064 (syntax error); MySQL 8 still does not implement it. Combine a left join with the unmatched rows of a right join:
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
UNION ALL
SELECT s.name, e.course
FROM students s
RIGHT JOIN enrolments e ON e.student_id = s.student_id
WHERE s.student_id IS NULL;
| name | course |
|---|---|
| Aman | Power BI |
| Aman | SQL |
| Bhavna | Python |
| Chirag | SQL |
| Divya | NULL |
| NULL | Tableau |
Both gaps appear: Divya with no course and Tableau with no student. (Add an ORDER BY if you need a fixed row order.) You will often see this written as LEFT JOIN ... UNION ... RIGHT JOIN without the WHERE. That also works on this data, but plain UNION removes duplicates, so it would wrongly collapse two genuinely identical rows. The UNION ALL + IS NULL version is safe. The difference is explained in UNION vs UNION ALL.
The mistake that turns a LEFT JOIN into an INNER JOIN
Goal: list every student and show their SQL enrolment if they have one. This looks right but is not:
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.course = 'SQL';
| name | course |
|---|---|
| Aman | SQL |
| Chirag | SQL |
The WHERE runs after the join and throws away every row where e.course is NULL, so Bhavna and Divya vanish. Move the condition on the right-hand table into the ON clause:
SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e
ON e.student_id = s.student_id
AND e.course = 'SQL'
ORDER BY s.student_id;
| name | course |
|---|---|
| Aman | SQL |
| Bhavna | NULL |
| Chirag | SQL |
| Divya | NULL |
Rule of thumb: in a left join, conditions on the right table go in ON; conditions on the left table go in WHERE.
Quick comparison
| Join | Rows returned on this data | Unmatched left rows (Divya) | Unmatched right rows (enrolment 105) |
|---|---|---|---|
| INNER JOIN | 4 | Dropped | Dropped |
| LEFT JOIN | 5 | Kept, right side NULL | Dropped |
| RIGHT JOIN | 5 | Dropped | Kept, left side NULL |
| FULL (emulated) | 6 | Kept | Kept |
Other mistakes to watch for
- Unexpected duplicate rows. Joining to a table with several matches per key multiplies rows; a
SUM()over the result then double-counts. Aggregate in a subquery or CTE first. - Missing join condition. Gives every combination of rows; see CROSS JOIN.
- Joining columns of different types (
INTtoVARCHAR), which can stop MySQL using indexes.
Next: join a table to itself in self JOIN in MySQL, and compare joins with subqueries in JOIN vs subquery performance.