Oracle LEFT JOIN Clause

Summary: in this tutorial, you will learn how to use the Oracle LEFT JOIN clause to join a table to another table using a left join.

Introduction to Oracle LEFT JOIN clause #

The LEFT JOIN is an optional clause of a SELECT statement. A LEFT JOIN clause allows you to merge rows from two tables. The first table is a left table, and the second table is a right table.

A LEFT JOIN returns all rows from the left table and matching rows from the right table.

Here’s the syntax of the LEFT JOIN clause:

SELECT
  column_list
FROM
  X
  LEFT JOIN Y ON X.id = Y.id;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, provide the left table (X) in the FROM clause.
Second, specify the right table (Y) in the LEFT JOIN clause.
Third, define a join condition (X.id = Y.id) after the ON keyword

In this syntax, the LEFT JOIN matches the values in the id column of the left table (X) with the values in the id column of the right table (Y).

If they are equal, the LEFT JOIN merges two rows from both tables into one and includes it in an intermediate row.

If the row from the left table does not have a matching row in the right table, the LEFT JOIN merges the row in the left table with the row in the right table. However, it fills the rows in the right table with NULLs.

The statement returns a result set that includes columns specified in the SELECT clause.

In this syntax, we use the equal to operator (=) to compare values. You can use other comparison operators such as >, >=, <, and <=. Additionally, you can combine multiple expressions using the AND and OR operators in the condition.

Visualizing how the Oracle left join works #

Suppose you have two tables X and Y, and want to merge rows from both tables using a left join:

The X table has two columns: id (key) and x.
The Y table has two columns: id (key) and y.

The left join includes all rows from the left table (X) that and matching values from the right table (Y):

The following Venn diagram is another way to illustrate how an Oracle left join works:

Oracle LEFT JOIN clause examples #

We’ll use the following orders and employees tables in the sample database:

The orders table stores the sales order header data. It has the salesman_id column that references the employee_id column in the employees table.

The salesman_id column is nullable, meaning that not all orders have a sales employee who is in charge of the orders.

The following statement uses a LEFT JOIN clause to merge rows from the orders and employees tables:

SELECT
  order_id,
  status,
  first_name,
  last_name
FROM
  orders
  LEFT JOIN employees ON employee_id = salesman_id
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

In this example:

orders is the left table.
employees is the right table.

The LEFT JOIN returns all rows from the orders table. For the rows in the orders table that does not have a matching row in the employees table, it fills the columns of the employees table, such as first_name and last_name, with NULLs.

Joining three tables using LEFT JOIN clauses #

To join three tables, you use two LEFT JOIN clauses. For example, the following statement uses two LEFT JOIN clauses to merge rows from three tables: orders, employees and customers:

SELECT
  order_id,
  name AS customer_name,
  status,
  first_name,
  last_name
FROM
  orders
  LEFT JOIN employees ON employee_id = salesman_id
  LEFT JOIN customers ON customers.customer_id = orders.customer_id
ORDER BY
  order_date DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Oracle LEFT JOIN with USING clause #

Oracle allows you to use the USING clause to implicitly test for equality (=) when joining tables.

Here’s the syntax of the LEFT JOIN with the USING clause:

SELECT
  column_list
FROM
  X
  LEFT JOIN Y USING (id);Code language: SQL (Structured Query Language) (sql)

In this example, the LEFT JOIN will check if the values in the id column of the X table is equal to the values in the id column of the Y table.

The following expressions are equivalent:

USING (id)
ON X.id = Y.id

The following statement uses a LEFT JOIN clause with the USING syntax to join the customers and orders tables:

SELECT
  name,
  order_id,
  status,
  order_date
FROM
  customers
  LEFT JOIN orders USING (customer_id)
ORDER BY
  name;Code language: SQL (Structured Query Language) (sql)

Try it

The statement returns all customers and their orders.

If a customer does not have any orders, the LEFT JOIN fills the columns of the orders table with NULLs.

Conditions in the ON vs. WHERE clause #

The following statement uses the LEFT JOIN clause to retrieve data from the orders and employees tables with the order id 58:

SELECT
  order_id,
  status,
  employee_id,
  last_name
FROM
  orders
  LEFT JOIN employees ON employee_id = salesman_id
WHERE
  order_id = 58
ORDER BY
  last_name NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

How the query works:

First, the WHERE clause includes one order with the order id 58.
Second, the LEFT JOIN clause merges the row of the order 58 with the matching row in the employees table.
Third, the statement returns a row with columns specified in the SELECT clause.

If you move the condition from the WHERE clause to the ON clause of the LEFT JOIN:

SELECT
  order_id,
  status,
  employee_id,
  last_name
FROM
  orders
  LEFT JOIN employees ON employee_id = salesman_id AND order_id = 58
ORDER BY
  last_name NULLS LAST;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, the query returns all rows from the orders table and matching rows form the employees table.

The LEFT JOIN matches rows from the orders table with rows from the employees table using the condition:

employee_id = salesman_id AND order_id = 58

Since the condition includes only the order with id 58, there is one row in the employees table meets the condition.

Summary #

Use a LEFT JOIN clause to select all rows from the left table and only rows from the right table.

Quiz #

Was this tutorial helpful?