LEFT JOIN

Left Join in MySQL

Introduction
Optional relationships need all records from one table regardless of matches.
LEFT JOIN keeps all rows from left table, NULLs for non-matching right table rows.
It is widely used for:

• Master-detail with missing details
• Optional foreign key relationships
• Complete reporting

What is Left Join
LEFT JOIN returns ALL rows from left table and matching rows from right table.
Non-matching right table rows show NULL values.

Basic Syntax

sql

SELECT columns 

FROM table1 

LEFT JOIN table2 ON table1.column = table2.column; 

Example

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id; 

All students + their courses (NULL for unenrolled).

Left Join with WHERE
Filter after join (careful with NULLs).

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

WHERE c.course_name IS NOT NULL;  -- Becomes INNER JOIN! 

Multiple Left Joins
Chain multiple optional relationships.

sql

SELECT s.name, c.course_name, d.department_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

LEFT JOIN Departments d ON c.dept_id = d.id; 

Left Join with ORDER BY
Sort including non-matching rows.

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

ORDER BY s.name; 

Left Join with GROUP BY
Aggregate with optional matches.

sql

SELECT s.city, COUNT(c.id) as course_count 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

GROUP BY s.city; 

Left Join with IS NULL
Find non-matching records (anti-join).

sql

SELECT s.name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

WHERE c.id IS NULL;  -- Students without courses 

Left Join with USING
Same column name shorthand.

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c USING(course_id); 

Left Join Multiple Conditions
Complex matching logic.

sql

SELECT s.name, c.course_name 

FROM Students s 

LEFT JOIN Courses c ON s.course_id = c.id 

                 AND c.status = 'Active'; 

Left Join vs INNER Join

Aspect	LEFT JOIN	INNER JOIN
Left table rows	All	Matching only
Right table rows	Matching + NULL	Matching only
Use case	Optional relation	Required match

Performance with Left Join
Index both join columns.

sql

-- Ensure indexes: Students(course_id), Courses(id) 

SELECT * FROM Students s LEFT JOIN Courses c ON s.course_id = c.id; 

Execution Order with LEFT JOIN
FROM → LEFT JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• WHERE c.column = value converts to INNER JOIN
• Index join columns (both sides)
• LEFT JOIN NULL finding = anti-join optimization
• Multiple LEFT JOINs chain left-to-right

Important Notes

• All left table rows preserved
• Non-matching right rows = NULL values
• WHERE on right table converts to INNER
• USING requires identical column names
• Cartesian if no ON condition

Example Scenario
Complete student report including unenrolled:

sql

SELECT

    s.name,

    s.course_id,

    c.course_name,

    CASE WHEN c.id IS NULL THEN 'No Course' ELSE 'Enrolled' END as status

FROM Students s

LEFT JOIN Courses c ON s.course_id = c.id

WHERE s.status = 'Active'

ORDER BY s.name, c.course_name;

Common Mistakes

• WHERE right_table.column = X (becomes INNER JOIN)
• No indexes on join columns
• Expecting right table all rows
• Functions preventing join optimization
• IS NULL in wrong position

Key Points to Remember

• LEFT JOIN keeps ALL left table rows
• Non-matches show NULL on right table
• WHERE right_table.col = X → INNER JOIN
• Use IS NULL to find non-matches
• Index both join columns
• Chaining multiple LEFT JOINs