LEFT & RIGHT JOIN

LEFT JOIN and RIGHT JOIN are "outer" joins. Unlike INNER JOIN which only returns matching rows, outer joins keep rows from one table even when there is no match in the other table. This is essential when you need to see all the data from one table, not just the matches.

LEFT JOIN

LEFT JOIN returns all rows from the left (first) table, and matching rows from the right table. When there is no match, the right table columns are filled with NULL:

SELECT
  s.first_name,
  s.last_name,
  e.course_id,
  c.name AS course_name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id;

This returns every student, even those who are not enrolled in any course. For students without enrollments, the course columns will show NULL.

Finding Unmatched Rows

A common use case for LEFT JOIN is finding rows that have no match:

-- Students who are NOT enrolled in any course
SELECT
  s.first_name,
  s.last_name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;

The trick is to use LEFT JOIN and then filter for NULL values in the right table. This finds students who have no enrollment records at all.

RIGHT JOIN

RIGHT JOIN is the mirror image of LEFT JOIN. It returns all rows from the right table and matching rows from the left:

SELECT
  c.name AS course_name,
  s.first_name,
  s.last_name
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id
RIGHT JOIN courses c ON e.course_id = c.id;

This shows all courses, even those with no students enrolled. In practice, RIGHT JOIN is used less often because you can always rewrite it as a LEFT JOIN by swapping the table order.

FULL JOIN

FULL JOIN returns all rows from both tables. When there is no match, NULL fills in the gaps:

SELECT
  s.first_name,
  c.name AS course_name
FROM students s
FULL JOIN enrollments e ON s.id = e.student_id
FULL JOIN courses c ON e.course_id = c.id;

This shows all students and all courses. Students without enrollments and courses without students both appear in the results.

LEFT JOIN vs INNER JOIN

The key difference is what happens to unmatched rows:

• INNER JOIN — Drops unmatched rows entirely. Only keeps matches.
• LEFT JOIN — Keeps all rows from the left table. Uses NULL for missing right-side data.

Use INNER JOIN when you only want data that exists in both tables. Use LEFT JOIN when you want everything from the left table regardless of whether there are matches.

Performance Consideration

OUTER JOINs (LEFT, RIGHT, FULL) can be slower than INNER JOINs because the database has to process all rows from one table even when there are no matches. Make sure you have indexes on the join columns for best performance.