LEFT JOIN and RIGHT JOIN

The Outer-Join Idea

An outer join combines matching pairs and also preserves a chosen input. LEFT JOIN preserves its left input; RIGHT JOIN preserves its right input. Missing partners are represented with NULL-extended columns.

LEFT JOIN with Verified Output

SELECT s.student_id, s.student_name, c.class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
ORDER BY s.student_id;

Five left rows guarantee at least five output rows. A left row can still produce more than one output row if several right rows match it. The NULL shown for Vihaan is generated by the outer join because no class row matches.

Equivalent RIGHT JOIN and Preferred Rewrite

SELECT s.student_name, c.class_name
FROM join_students AS s
RIGHT JOIN classes AS c
  ON c.class_id = s.class_id
ORDER BY c.class_id, s.student_id;

This keeps all classes, including XI-A. The same result is usually easier to read as:

SELECT s.student_name, c.class_name
FROM classes AS c
LEFT JOIN join_students AS s
  ON s.class_id = c.class_id
ORDER BY c.class_id, s.student_id;

RIGHT JOIN is valid MySQL syntax; the rewrite is a style choice, not a different capability.

Anti-Join: Find Unmatched Rows

SELECT s.student_id, s.student_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
WHERE c.class_id IS NULL;

Reverse the preserved side to find classes without students:

SELECT c.class_id, c.class_name
FROM classes AS c
LEFT JOIN join_students AS s
  ON s.class_id = c.class_id
WHERE s.student_id IS NULL;

ON Versus WHERE Changes the Question

Preserve all students; allow only X-A to match

SELECT s.student_name, c.class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
 AND c.class_name = 'X-A'
ORDER BY s.student_id;

Keep only final rows belonging to X-A

SELECT s.student_name, c.class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
WHERE c.class_name = 'X-A';

Neither query is universally better; they answer different requirements. State the requirement before placing the predicate.

Counts Including Zero

SELECT c.class_name,
       COUNT(s.student_id) AS student_count
FROM classes AS c
LEFT JOIN join_students AS s
  ON s.class_id = c.class_id
GROUP BY c.class_id, c.class_name
ORDER BY c.class_id;

COUNT(expression) ignores NULL. Counting the child primary key therefore distinguishes zero children from one match. Group by the stable class identifier as well as the displayed name.

Mistakes, Performance and Practice

• Do not put a NULL-rejecting right filter in WHERE when the left population must remain.
• Do not test a nullable right attribute to detect absence; test its non-nullable key.
• Do not assume outer joins prevent row multiplication.
• Index join keys and inspect EXPLAIN only after verifying meaning and row counts.

Practice: preserve all teachers/classes and show zero students; write both RIGHT and LEFT forms; then explain why COUNT(*) is wrong for XI-A in this dataset.

Official References

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Outer Join Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax and optimizer notes were checked against the official MySQL 8.4 manual. Always test plans and row counts on your own schema and data.