SQL LEFT JOIN

9
0

Introduction

A database report may need to include records even when related information is missing. Combining tables using only matching rows can leave out records that are important to the report.

LEFT JOIN handles this requirement by preserving every row from the left table during the join. Understanding its matching rules, NULL values, and interaction with filters helps produce complete and accurate results.

What Is a LEFT JOIN?

A LEFT JOIN, also called a LEFT OUTER JOIN, combines rows from two tables according to a join condition. It returns matching row pairs and preserves unmatched rows from the left table.

Its basic behavior is:

  • A match exists: Combine the left-table row with each matching right-table row.

  • No match exists: Keep the left-table row and fill the right-table columns with NULL.

  • An unmatched row exists only in the right table: Exclude that row.

In a simple two-table query, the left table is the table written before LEFT JOIN. The order of columns in SELECT does not determine which table is preserved.

LEFT JOIN and LEFT OUTER JOIN have the same meaning; OUTER is optional. A later WHERE condition can still remove rows from the joined result.

Example Tables

Consider a college database containing two tables.

The Department table stores the departments offered by the college. A department can exist even when no students are assigned to it.

Department

DeptID

DeptName

10

Computer Science

20

Electronics

30

Mathematics

The Student table stores each student’s identifier, name, assigned department, and semester.

Student

StudentID

Name

DeptID

Semester

101

Asha

10

2

102

Ravi

20

4

103

Meera

10

2

104

Neha

NULL

2

Department.DeptID and Student.StudentID are primary keys. A non-NULL value in Student.DeptID refers to an existing department.

Asha and Meera belong to Computer Science, while Ravi belongs to Electronics. Mathematics has no students. Neha has no assigned department, represented by NULL.

Using LEFT JOIN to Include Every Department

Objective: Display every department together with its students, including departments that have no students.

Department must be the left table because every department needs to remain in the joined result.

Consider the following queries to list all departments and their matching students.

SELECT d.DeptID, d.DeptName, s.StudentID, s.Name
FROM Department AS d
LEFT JOIN Student AS s
ON d.DeptID = s.DeptID
ORDER BY d.DeptID, s.StudentID;

AS d and AS s assign temporary aliases, or shorter names, to Department and Student. For example, d.DeptID refers to the department table’s ID column.

The ON condition matches equal department IDs. ORDER BY d.DeptID, s.StudentID sorts by department ID first and student ID within each department, both in ascending order.

Expected result

DeptID

DeptName

StudentID

Name

10

Computer Science

101

Asha

10

Computer Science

103

Meera

20

Electronics

102

Ravi

30

Mathematics

NULL

NULL

The result illustrates three important behaviors:

  • Multiple matches: Computer Science appears twice because it matches two students.

  • An unmatched left-table row: Mathematics remains, with NULL student details.

  • An unmatched right-table row: Neha does not appear because she has no matching department.

A left join therefore does not guarantee exactly one result row per left-table row. Each matching pair contributes a row.

The displayed DeptID comes from Department, so Mathematics retains its ID, 30. Selecting s.DeptID instead would show NULL for that row because no student matched it.

These NULL values appear only in the query result. The join does not insert or modify records in either source table.

HOW LEFT JOIN WORKS

HOW LEFT JOIN WORKS

Finding Departments Without Students

Objective: Return only departments that have no matching student.

The left join preserves these departments with NULL values in the student columns. Checking the student’s primary key identifies those unmatched rows.

Consider the following queries to find departments without students.

SELECT d.DeptID, d.DeptName
FROM Department AS d
LEFT JOIN Student AS s
ON d.DeptID = s.DeptID
WHERE s.StudentID IS NULL
ORDER BY d.DeptID;

Expected result

DeptID

DeptName

30

Mathematics

IS NULL checks whether a value is NULL. Since an actual student’s primary key cannot be NULL, s.StudentID IS NULL identifies the absence of a matching student.

Use IS NULL for this check, rather than = NULL. Ordinary equality comparisons involving NULL do not evaluate to true.

Checking a primary key is useful here because a nullable column, such as an optional phone number, could contain NULL even when a matching record exists.

LEFT JOIN Compared with Other Joins

The main difference is which unmatched rows are preserved during the join.

Join type

Rows preserved during the join

INNER JOIN

Matching pairs only

LEFT JOIN

Every left-table row, with matching right-table data

RIGHT JOIN

Every right-table row, with matching left-table data

With Department on the left and Student on the right, an inner join would omit Mathematics. The left join retains it with NULL student details.

The same department-preserving result can be written using a right join by placing Student on the left and Department on the right, while retaining the matching condition and selected columns. Changing the join direction without swapping the tables changes which side is preserved.

Summary

LEFT JOIN preserves every row from the left table during the join and combines it with matching rows from the right table. Missing matches produce NULL values in right-table columns, while multiple matches produce multiple result rows.

The placement of conditions matters: ON controls matching, and WHERE filters the joined result. Checking a non-nullable right-table key with IS NULL finds unmatched records, while counting that key supports reports that include zero matches.

CS Core

Read Similar Blogs

Comments0