Description

SQL LEFT JOIN can be used to join two tables, which returns all the rows from the left table along with the data from the right table that met the join condition.

In the case of rows that don't satisfy the join condition, a blank value is assigned.

The left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

  • It is a type of outer join, so it is also referred to as the left outer join.
  • It can be implemented on SELECT, UPDATE, and DELETE statements.

Also, check the other types of outer join - right join and full join.

Syntax

Here is the basic syntax of a SELECT statement with a LEFT JOIN.

  • The table1 and table2 represents the left and right table names.
  • The column_list represents the column names from either or both the tables.
  • The column1 and column2 are the columns from the left and right tables, which are used for creating the LEFT JOIN.
SELECT column_list
FROM table1
LEFT JOIN table2 ON table1.column1 = table2.column2;

Table Data

Consider the below data in the orders, customers, and shippers tables, with the below observations.

  • Tables orders and customers are relationally connected via customer_id column.
  • Tables orders and shippers are relationally connected via shipper_id column.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Left Join

The below SQL statement fetches data from both tables by joining them together as mentioned below.

  • Returns all the rows from the left table along with the required data from the right table.
  • In case the right table doesn't have the corresponding data, then NULL is assigned.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains all rows from the left table along with the required data from the right table, as shown below.

  • For the last order record, a corresponding customer record is not found, so a NULL value is used in the result set.
order_id order_date order_value customer_name
10250 2014-07-05 1807.5 Dominique Perrier
10251 2014-07-15 1159 Fran Wilson
10252 2014-07-18 2822 Maria Anders
10253 2014-07-19 2575.3 Dominique Perrier
10254 2014-07-24 1256.25 Maria Anders
10255 2014-07-31 9247.5 NULL

Left Join All Columns

The below SQL statement fetches data from both the tables as mentioned below.

  • The SELECT * returns all the columns from both the tables, without repeating the joined columns.

Run this on IDE

SELECT *
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains data from both the joined tables as shown below.

  • For the last order record, a corresponding customer record is not found, so all its columns contain a blank in the result set.
order_id order_date order_value customer_id supplier_id customer_name address city country postal_code
10250 2014-07-05 1807.5 3 1 Dominique Perrier 25, rue Lauriston Paris France 75016
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10252 2014-07-18 2822 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10253 2014-07-19 2575.3 3 2 Dominique Perrier 25, rue Lauriston Paris France 75016
10254 2014-07-24 1256.25 1 3 Maria Anders Obere Str. 57 Berlin Germany 12209
10255 2014-07-31 9247.5 5 1 NULL NULL NULL NULL NULL

Tips & Recommendations

An outer join is a join that includes rows in the result set even though there may not be a match between the rows of the joined tables.

In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

In an outer join, wherever the database system can't find a match record, it places NULL values to indicate that the data do not exist in columns of such table.

Overall

We now know how can we use a SELECT Statement with LEFT JOIN to combine tables and fetch data from the joined tables.

Related Links