Imagine we have two tables: one is the students table (recording basic student information like name and age), and the other is the scores table (recording each student’s exam scores). If we want to know each student’s specific scores, we need to “combine” the data from these two tables—this is what MySQL’s JOIN operation does!

1. First, Understand the Table Structures¶

Assume we have two tables:
- Students Table (students): Records student IDs and basic information
| id | name | age |
|-----|------|-----|
| 1 | 小明 | 18 |
| 2 | 小红 | 19 |
| 3 | 小刚 | 17 |
| 4 | 小强 | 18 |

• Scores Table (scores): Records student scores, linked to the students table via student_id
  | id | student_id | score |
  |-----|------------|-------|
  | 1 | 1 | 90 | (小明’s score)
  | 2 | 2 | 85 | (小红’s score)
  | 3 | 3 | 78 | (小刚’s score)
  | 4 | 5 | 95 | (score for student_id=5, but no such student in the students table)

2. Inner Join (INNER JOIN)¶

What is Inner Join?
Inner Join returns only rows where matching records exist in both tables (“only show data present in both tables”).

Syntax:

SELECT column_names FROM table1 INNER JOIN table2 ON table1.join_column = table2.join_column;

(INNER JOIN can be simplified to JOIN with the same effect.)

Example:
View names and scores of students with score records:

SELECT students.name, scores.score 
FROM students 
INNER JOIN scores 
ON students.id = scores.student_id;

Result:
Only includes matching records from both tables (小明, 小红, 小刚):
| name | score |
|------|-------|
| 小明 | 90 |
| 小红 | 85 |
| 小刚 | 78 |

3. Left Join (LEFT JOIN)¶

What is Left Join?
Left Join returns all records from the left table, with NULL filling in unmatched parts of the right table.

Syntax:

SELECT column_names FROM table1 LEFT JOIN table2 ON table1.join_column = table2.join_column;

Example:
View all students’ names and scores (including those with no scores):

SELECT students.name, scores.score 
FROM students 
LEFT JOIN scores 
ON students.id = scores.student_id;

Result:
All students from the left table (students) are retained; NULL for unmatched right table entries:
| name | score |
|------|-------|
| 小明 | 90 |
| 小红 | 85 |
| 小刚 | 78 |
| 小强 | NULL |

4. Right Join (RIGHT JOIN)¶

What is Right Join?
Right Join returns all records from the right table, with NULL filling in unmatched parts of the left table.

Syntax:

SELECT column_names FROM table1 RIGHT JOIN table2 ON table1.join_column = table2.join_column;

Example:
View student names corresponding to all score records (including scores with no student):

SELECT students.name, scores.score 
FROM students 
RIGHT JOIN scores 
ON students.id = scores.student_id;

Result:
All records from the right table (scores) are retained; NULL for unmatched left table entries:
| name | score |
|------|-------|
| 小明 | 90 |
| 小红 | 85 |
| 小刚 | 78 |
| NULL | 95 |

5. Full Join (FULL JOIN)¶

Note: MySQL does not natively support FULL JOIN; it must be simulated using LEFT JOIN + UNION.

Syntax Example:

(SELECT students.name, scores.score 
 FROM students 
 LEFT JOIN scores ON students.id = scores.student_id)
UNION
(SELECT students.name, scores.score 
 FROM students 
 RIGHT JOIN scores ON students.id = scores.student_id)
WHERE students.name IS NOT NULL OR scores.score IS NOT NULL;

Result:
Includes all students and all scores, with NULL for unmatched parts:
| name | score |
|------|-------|
| 小明 | 90 |
| 小红 | 85 |
| 小刚 | 78 |
| 小强 | NULL |
| NULL | 95 |

6. Common Questions and Notes¶

• Is the ON clause mandatory?
  Yes! The ON clause specifies the join condition (e.g., students.id = scores.student_id). Without it, INNER JOIN becomes a Cartesian product (result rows = left table rows × right table rows, usually incorrect).

• What if the join condition is wrong?
  If the ON condition is incorrect (e.g., students.id = scores.id), the association fails, leading to empty results or incorrect data.

• How to filter NULL values?
  To find “students with no scores”:

  SELECT students.name 
  FROM students 
  LEFT JOIN scores ON students.id = scores.student_id 
  WHERE scores.score IS NULL; -- Only shows 小强

Summary¶

• INNER JOIN: Keeps only matching records from both tables (Recommended: Use when data must exist in both tables).
• LEFT JOIN: Keeps all records from the left table, with NULL for unmatched right table entries (Recommended: Use when retaining all main table data).
• RIGHT JOIN: Keeps all records from the right table, with NULL for unmatched left table entries (Recommended: Use when retaining all secondary table data).

Remember the core logic: “The left table retains all, the right table matches as needed” to master JOIN easily!