Previous
Written by: rJTutorial Team | Last Updated:

SQL LEFT JOIN

In relational databases, data is often spread across multiple tables to avoid redundancy. The LEFT JOIN (also known as a LEFT OUTER JOIN) is one of the most common tools developers use to pull that data back together. It allows you to retrieve all records from your primary "left" table, along with any matching information from a "right" table. If no match is found, SQL doesn't discard the row; instead, it fills the missing columns with NULL values.

Developer Tip: In standard SQL, LEFT JOIN and LEFT OUTER JOIN are functionally identical. Most developers prefer LEFT JOIN because it is shorter and easier to read.

 

Syntax of LEFT JOIN

To perform a LEFT JOIN, you specify the two tables you want to connect and the column (the "key") that links them together.

SELECT 
    table1.column1, 
    table2.column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
  • table1: This is your "Left" table. Every row from this table will appear in your final result.
  • table2: This is your "Right" table. Data from this table will only appear if it matches table1.
  • ON: This is the logic that tells SQL how the tables are related (usually via an ID).
Best Practice: Always use table aliases (e.g., FROM employees AS e) to make your queries shorter and to avoid "ambiguous column" errors when both tables have columns with the same name.

 

Example

Imagine you are building an HR dashboard. You have a list of all employees and a list of department assignments. Not every employee has been assigned a department yet (perhaps they are new hires).

Employees Table

employee_id first_name last_name
1 John Doe
2 Jane Smith
3 Mike Johnson

Departments Table

department_id employee_id department_name
101 1 HR
102 2 IT

In this scenario, Mike Johnson (ID 3) exists in the Employees table but has no corresponding entry in the Departments table. If we used a standard INNER JOIN, Mike would disappear from our results entirely. By using a LEFT JOIN, we keep Mike in the list:

SELECT 
    employees.first_name, 
    employees.last_name, 
    departments.department_name
FROM employees
LEFT JOIN departments
ON employees.employee_id = departments.employee_id;

Output:

first_name last_name department_name
John Doe HR
Jane Smith IT
Mike Johnson NULL

In this result:

  • The LEFT JOIN ensures every employee is listed.
  • John and Jane have matching IDs in the departments table, so their department names are pulled in.
  • Because Mike Johnson's ID (3) is missing from the departments table, the department_name column returns NULL.
Watch Out: If the "Right" table has multiple matching rows for a single row in the "Left" table, the result will duplicate the "Left" row for every match found. This is a common cause of unexpected data inflation in reports.

 

LEFT JOIN with WHERE Clause

A powerful real-world use case for the LEFT JOIN is finding "missing" data. By adding a WHERE clause that looks for NULL values in the right table, you can identify records that don't have a relationship yet.

-- Find all employees who have NOT been assigned to a department
SELECT employees.first_name, employees.last_name
FROM employees
LEFT JOIN departments ON employees.employee_id = departments.employee_id
WHERE departments.department_name IS NULL;

This is often called an "Anti-Join." It is incredibly useful for data auditing, such as finding customers who haven't placed an order or products that don't have descriptions.

Output:

first_name last_name department_name
Mike Johnson NULL
Common Mistake: Be careful when filtering the "Right" table in your WHERE clause. If you write WHERE departments.status = 'Active', it will filter out the NULLs and effectively turn your LEFT JOIN back into an INNER JOIN. If you need to filter the right table, put that condition inside the ON clause instead.

 

LEFT JOIN with Multiple Tables

You aren't limited to just two tables. You can chain LEFT JOIN statements together to build a complete picture of your data. For example, if we want to see employees, their departments, and any projects they are currently working on:

SELECT 
    e.first_name, 
    e.last_name, 
    d.department_name, 
    p.project_name
FROM employees AS e
LEFT JOIN departments AS d ON e.employee_id = d.employee_id
LEFT JOIN projects AS p ON e.employee_id = p.employee_id;

In this query, the "Left" table starts as employees. The result of that first join then becomes the new "Left" side for the projects join. This ensures that even an employee with no department and no projects will still appear in your final report.

 

Key Points

  • Completeness: LEFT JOIN returns all rows from the left table, regardless of whether a match exists on the right.
  • NULL Handling: When no match is found, the right-side columns are filled with NULL.
  • Direction Matters: Swapping the tables (making Table A the right table instead of the left) will completely change your result set.
  • Filtering: Use IS NULL in the WHERE clause to find orphaned records in your database.

 

Summary

The LEFT JOIN is your go-to tool when you need a comprehensive list from one table and optional data from another. Whether you are generating payroll reports, auditing missing entries, or joining multiple data sources, understanding how to preserve your "Left" table records while handling "Right" table NULL values is a fundamental skill for any developer working with SQL.

Previous