SQL LEFT JOIN

Last Updated : 23 May, 2026

In SQL, the LEFT JOIN (also called LEFT OUTER JOIN) retrieves all records from the left table and only the matching records from the right table. If no match is found, NULL values are returned for the right table columns.

  • Returns all rows from the left table.
  • Includes matching rows from the right table.
  • Displays NULL values for non-matching rows from the right table.
  • Helps retrieve complete data from the left table.
left_join
SQL LEFT JOIN

Syntax:

SELECT column_name(s) 
FROM tableA 
LEFT JOIN tableB ON tableA.column_name = tableB.column_name;
  • SELECT column_name(s): Specifies the columns to retrieve from the tables.
  • FROM table: Defines the left table from which all rows will be returned.
  • LEFT JOIN table: Joins the right table with the left table.
  • ON tableA.column_name = tableB.column_name: Specifies the matching condition between both tables

Examples of SQL LEFT JOIN

First, we will create a demo SQL database and tables. Consider two tables: Emp (employees) and department (departments). The Emp table contains employee details, while the department table holds department details.

Employee Table:

Emplo-yee

Department Table:

depart-1

Example 1: Performing a LEFT JOIN

To perform left-join on Employee and Department Tables we will use the following SQL query:

Query:

SELECT Emp.EmpID, Emp.Name, department.
department_name, department.department_head, 
department.location 
FROM Emp
LEFT JOIN department ON Emp.department_id = department.department_id;

Output:

1-1
  • All employees (left table) are included.
  • Since each employee is assigned a department, we see matching details from the right table.
  • If an employee had no department assigned, the department columns would show NULL

Example 2: SQL LEFT JOIN with WHERE Clause

In this example, the WHERE clause filters the results to display only employees whose department location is 'London'. Departments without employees will not appear in the output.

Query: 

SELECT e.EmpID, e.Name, d.department_name, d.department_head, d.location 
FROM Emp e
LEFT JOIN department d ON e.department_id = d.department_id
WHERE d.location = 'Bangalore';

Output: 

1-2
  • Only employees working in departments located in Bangalore are included.
  • If no department matched Bangalore, the result would be empty.

Example 3: SQL LEFT JOIN as Aliases

In this example, aliases e and d are used for the tables to make the query shorter, simpler and easier to read.

Query:

SELECT e.EmpID, e.Name, d.department_name, 
d.department_head, d.location 
FROM Emp e
LEFT JOIN department d ON 
e.department_id = d.department_id;

Output:

1-3
  • e is used as an alias for Emp and d is used for department.
  • This improves query readability and makes referencing columns simpler.
Comment
Article Tags:
SQL
DBMS-SQL

Explore