Oracle LEFT JOIN By Practical Examples

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.

Send Cancel