Oracle LEFT JOIN

Summary: in this tutorial, you will learn how to use the Oracle LEFT JOIN clause to query data from multiple tables.

Introduction to Oracle LEFT JOIN clause

The following statement illustrates the syntax of the LEFT JOIN clause when joining two tables T1 and T2:

SELECT
    column_list
FROM
    T1
LEFT JOIN T2 ON
    join_predicate;
Code language: SQL (Structured Query Language) (sql)

In this query, T1 is the left table and T2 is the right table.

The query compares each row in the T1 table with rows in the T2 table.

If a pair of rows from both T1 and T2 tables satisfy the join predicate, the query combines column values from rows in both tables and includes this row in the result set.

In case a row in the T1 table does not have any matching rows in the T2 table, the query combines column values from the row in the T1 table with a NULL value for each column in the right table that appears in the SELECT clause.

In other words, a left join returns all rows from the left table and matching rows from the right table.

Oracle LEFT JOIN examples

See 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 to 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 retrieves all orders and employee data from both 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)

The following picture illustrates the result:

The result includes all rows from the orders table. For the rows in the orders table that do not have a matching row in the employees table, NULL values are used.

Oracle LEFT JOIN – join multiple tables

The following statement uses LEFT JOIN clauses to join 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)

Oracle LEFT JOIN with USING clause

The USING clause specifies which column to test for equality when you join tables. The following shows the syntax of the LEFT JOIN with the USING clause:

SELECT
    column_list
FROM
    T1
LEFT JOIN T2 USING(c1,c2,c3, ...);
Code language: SQL (Structured Query Language) (sql)

In this statement, the columns listed in the USING clause must be presented in both T1 and T2 tables.

The statement that uses the USING clause above is equivalent to the following statement which uses the ON clause:

SELECT
    column_list
FROM
    t1
LEFT JOIN t2 ON
    t1.c1 = t2.c1
    AND t1.c2 = t2.c2
    AND t1.c3 = t2.c3
    AND ... ;
Code language: SQL (Structured Query Language) (sql)

The following statement demonstrates how to use the LEFT JOIN with the USING statement:

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)

This statement retrieved all customers and their orders. For the customers who have not placed any orders, null is used for the columns of the orders table that appear in the SELECT clause, which are order_id, status, and order_date.

Conditions in ON vs. WHERE clause

The following statement gets the order and salesman data of order 58.

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

Now 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;
Code language: SQL (Structured Query Language) (sql)

In this case, the query returns all orders but only order 58 had the salesman data associated with it.

Note that for the inner join, the condition placed in the ON has the same effect as it is placed in the WHERE clause.

In this tutorial, you have learned how to use the Oracle LEFT JOIN clause to retrieve data from multiple tables.