⬅️ The LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no matching row in the right table, SQL returns NULL for the right-table columns.

📖 What is a LEFT JOIN?

A LEFT JOIN keeps every record from the left table, regardless of whether a matching record exists in the right table. It is useful when you want complete information from one table while including related data from another table whenever available.

🎯 Why Use LEFT JOIN?

Use a LEFT JOIN when every record from the left table is important, even if related data is missing.

📊 Sample Tables

Consider the following Students table:

Consider the following Courses table:

📝 Basic Syntax

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

💡 Basic LEFT JOIN Example

Retrieve every student along with their enrolled course, if one exists.

SELECT Students.Name,
       Courses.CourseName
FROM Students
LEFT JOIN Courses
ON Students.StudentID = Courses.StudentID;

Result:

🏷️ Using Table Aliases

Aliases make join queries easier to read.

SELECT s.Name,
       c.CourseName
FROM Students AS s
LEFT JOIN Courses AS c
ON s.StudentID = c.StudentID;

🔍 LEFT JOIN with WHERE

You can filter the joined results using the WHERE clause.

SELECT s.Name,
       c.CourseName
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID
WHERE s.Department = 'Computer Science';

🚫 Finding Unmatched Rows

One of the most common uses of LEFT JOIN is finding records in the left table that have no matching rows in the right table.

SELECT s.Name
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID
WHERE c.StudentID IS NULL;

This query returns students who are not enrolled in any course.

📊 LEFT JOIN with ORDER BY

Sort the joined results using the ORDER BY clause.

SELECT s.Name,
       c.CourseName
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.Name ASC;

🔗 LEFT JOIN Multiple Tables

You can join multiple related tables in a single query.

SELECT s.Name,
       c.CourseName,
       d.DepartmentName
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID
LEFT JOIN Departments d
ON s.DepartmentID = d.DepartmentID;

📊 How LEFT JOIN Works

⚖️ INNER JOIN vs LEFT JOIN

⚠️ Common Mistakes

💼 Real-World Example

A university administrator wants a report listing every student and the course they are enrolled in. Students without any course enrollment should still appear in the report.

SELECT s.StudentID,
       s.Name,
       s.Department,
       c.CourseName
FROM Students s
LEFT JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.Name;

This query ensures that every student is included, even if no course has been assigned.

⚠️ Best Practices

🚀 Key Points to Remember