The LEFT JOIN

Definition: The LEFT JOIN (or Left Outer Join) returns all records from the "left" table (the first table mentioned in the query) and the matched records from the "right" table. If there is no match, the result will contain NULL values for every column of the right table.

Why: Beginner SQL topic outlines often introduce outer joins immediately after inner joins to show how to handle "incomplete" data. While an inner join hides rows without a match, a LEFT JOIN allows you to keep your primary list intact while seeing what related information is available.

Syntax

The structure is identical to an Inner Join, but the keyword changes to LEFT JOIN. The "Left" table is the one following FROM, and the "Right" table is the one following LEFT JOIN.

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Example: Finding All Students (Even Those Without Courses)

If you want to see a list of all students, including those who haven't enrolled in any courses yet, you would use a LEFT JOIN:

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

Explanation

• Left Table Priority: The students table is the left table. This query guarantees that every student's name will appear in the final list at least once.
• Handling Non-Matches: If a student (like "Ravi") has not enrolled in a course, the course_id column will simply show NULL next to his name.
• Inner vs. Left: In an INNER JOIN, Ravi would have been deleted from the results entirely because he didn't have a matching enrollment record.

Common Use Cases

• Identifying Gaps: Finding customers who have never placed an order or students who haven't registered for classes.
• Full Lists: Creating a master directory where you want to show everyone, even if they don't have secondary data associated with them.
• Safety: Using a LEFT JOIN is often safer when you aren't sure if a relationship exists but you don't want to lose the primary data in your result set.

Key Notes

• Order Matters: If you swap the table names (putting enrollments first), the results will change completely. In a Left Join, the "Main" table must always come first.
• Filtering on NULL: You can find the students who haven't enrolled by adding WHERE enrollments.course_id IS NULL to the end of the query.
• Standardization: LEFT JOIN and LEFT OUTER JOIN are functionally the same in SQL; the "OUTER" keyword is optional.