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
SELECT column_list FROM T1 LEFT JOIN T2 ON join_predicate;
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
If a pair of rows from both
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 row 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
In other words, a left join returns all rows from the left table and matching rows from the right table.
LEFT JOIN examples
See the following
employees tables in the sample database:
orders table stores the sales order header data. It has the
salesman_id column that references to the
employee_id column in the
salesman_id column is null-able, meaning that not all orders have a sales employee who is in charge of the orders.
The following statement retrieves all orders and employees data from both
SELECT order_id, status, first_name, last_name FROM orders LEFT JOIN employees ON employee_id = salesman_id ORDER BY order_date DESC;
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.
LEFT JOIN – join multiple tables
The following statement uses
LEFT JOIN clauses to join three tables:
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;
LEFT JOIN with
USING clause specifies which column to test for equality when you join tables. The following shows the syntax of the
LEFT JOIN with the
SELECT column_list FROM T1 LEFT JOIN T2 USING(c1,c2,c3, ...);
In this statement, the columns listed in the
USING clause must be presented in both
The statement that uses the
USING clause above is equivalent to the following statement which uses the
SELECT column_list FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3 AND ... ;
The following statement demonstrates how to use the
LEFT JOIN with the
SELECT name, order_id, status, order_date FROM customers LEFT JOIN orders USING(customer_id) ORDER BY name;
This statement retrieved all customers and their orders. For the customers who have not placed any orders, null are used for the columns of orders table that appears in the
SELECT clause, which are
The following statement gets the order and salesman data of the order 58.
SELECT order_id, status, employee_id, last_name FROM orders LEFT JOIN employees ON employee_id = salesman_id WHERE order_id = 58;
Now if you move the condition from the
WHERE clause to the
ON clause of the
SELECT order_id, status, employee_id, last_name FROM orders LEFT JOIN employees ON employee_id = salesman_id AND order_id = 58;
In this case, the query returns all orders but only the 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
In this tutorial, you have learned how to use the Oracle
LEFT JOIN clause to retrieve data from multiple tables.