SQL LEFT JOIN

The SQL LEFT JOIN returns all rows in the left table (table A), and the matched rows from the right table (table B). If there is no match, the result set from the right side is set to NULL.

The LEFT JOIN is a type of outer join that's why it can also be called LEFT OUTER JOIN.

The following illustration shows how left join works:

Syntax

The syntax for the LEFT JOIN is as follows:

SELECT column_list
FROM tableA
LEFT JOIN tableB
ON tableA.column_name = tableB.column_name;

Demo Table

To understand the LEFT JOIN, let us consider the following "Employee" and "Department" tables :

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | NULL    |
  | 1006   | James       | Johnson   | 8000      | NULL    |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL LEFT JOIN Example

Now, suppose that you need to select the employee number, first name, last name, and department name for all the employees, whether they are attached to a department or not. The left join will help us to get such a result set.

The following SQL statement will select the employee's number, first name, last name, and department name by joining the "Employee" and the "Department" tables using the common column "dept_id".

The LEFT JOIN will also include those employees who are not attached to a department.

SELECT em.emp_no, em.first_name, em.last_name, dep.dept_name
FROM employee AS em
LEFT JOIN department AS dep
ON em.dept_id = dep.dept_id;

Note: In an SQL statement, the left table is located in the left-hand of the JOIN clause, and the right table is located in the right-hand of the JOIN clause.

After running the above SQL statement, the output will look like the following:

+--------+-------------+-----------+------------------+
| emp_no | first_name  | last_name | dept_name        |
+--------+-------------+-----------+------------------+
| 1001   | James       | Smith     | Customer Service |
| 1002   | Maria       | Martinez  | Development      |
| 1003   | Andrew      | Rodriguez | Finance          |
| 1004   | Robert      | Hernandez | Development      |
| 1005   | Donna       | Williams  | NULL             |
| 1006   | James       | Johnson   | NULL             |
| 1007   | Stephanie   | Miller    | Sales            |
| 1008   | Nelson      | Lopez     | Sales            |
+--------+-------------+-----------+------------------+

As you can see above, the left join includes all the rows from the "Employee" table in the result set, even if there is no match on the "dept_id" column.

Note: The LEFT JOIN returns all the rows from the left table, even if there are no matches in the right table.