SQL Join Examples in MySQL

Dataset and a Professional Method

These examples use the five join_students and three classes rows created in the JOIN overview. Recreate that lab if needed. Before every query, write the required population, matching rule and expected row count.

Example 1–2: Match Students and Classes

1. Assigned students with classroom

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

2. Students taught by Ms. Rao

SELECT s.student_name
FROM join_students AS s
JOIN classes AS c
  ON c.class_id = s.class_id
WHERE c.teacher_name = 'Ms. Rao'
ORDER BY s.student_name;

The first query is many-to-one: each assigned student matches one class. The second filters the already matched population by a class attribute.

Example 3: Preserve Every Student

SELECT s.student_name,
       COALESCE(c.class_name, 'Not assigned') AS class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
ORDER BY s.student_id;

COALESCE is only display logic. The stored class_id remains NULL. Never replace a meaningful missing relationship with invented database data.

Example 4–5: Find Missing Relationships

4. Students without a class

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;

5. 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;

These are anti-join patterns. Test the other side's non-nullable primary key, not a nullable descriptive column, so a genuine NULL value cannot be mistaken for a missing row.

Example 6: Class Counts Including Zero

SELECT c.class_id, 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(s.student_id) ignores the NULL placeholder for XI-A. COUNT(*) would incorrectly report one joined row for that preserved class.

Example 7: Filter Matches Without Losing Students

Suppose the report must list all students but show class data only for X-A. Put the right-table restriction in ON:

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;

Moving c.class_name = 'X-A' to WHERE returns only Aarav and Meera. That may be correct for a different question, but it no longer preserves every student.

Debugging Checklist and Exercises

1. Qualify ambiguous columns with aliases.
2. Check whether the ON condition includes every key part.
3. Compare actual and predicted row counts.
4. Temporarily select both join keys to inspect mismatches.
5. Use ORDER BY only when deterministic display order matters.
6. Run EXPLAIN after the result is logically correct.

Practice: show every teacher with student count; list classes with at least two students; and modify Example 7 to show only X-B matches while retaining all five students.

Official References

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Nested 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.