LEFT JOIN

This join type returns records from the left table and those with a matching value in the right table. It can be extremely useful when it’s desirable to ensure every row from the left table involved in a JOIN operation is represented in the result set. Before continuing further, let’s first consider the two tables below. We’ll use these two tables in our first real-world problem. The data used throughout the examples on this page can be found at livesql.oracle.com.

Customers
The customers table contains one row per customer.

Orders
The orders table contains one row per order submitted by a customer.

The relationship between these two tables is: a customer may submit zero of more orders and an order is tied to or submitted by a single customer.

Let’s now return the idea of representing every row in the left table within our result set by introducing our first real-world problem.

Real-world Problem 1
Suppose, you’re tasked with generating a report consisting of all customers and their corresponding submitted orders. A key requirement of this report is to include those customers who have submitted an order and those that have not. Using the two tables above, a LEFT JOIN with the customers table on the left and the orders table on the right will return a result set capturing every customer. In other words, one row per customer or 319 rows. In instances where a customer has not submitted an order, the customer’s ID, first name, last name, and email address will be populated, and the specific customer will be represented within a single row. In those rows, the columns associated with an order will not be populated (i.e., NULL). For those customers having submitted a single order, the customer will still be represented within a single row; however, the columns associated with the order submitted will be populated. For customers having submitted multiple orders, each order will be represented within a single row and accompanied by the customer data associated with the customer having submitted the order. For example, if a customer submitted three orders, then each of those orders will be represented in their own row and the customer data will be replicated across those three rows. The previous few notes on what to expect in the result set stem from the aforementioned relationship between the two tables.

Below is the code required to meet this problem’s reporting requirements and the corresponding result set returned. Notice that the join criteria used specifies to return records from the left table and those with a matching customer ID value in the right table. The ON clause takes advantage of the primary key-foreign key relationship. Customer ID serves as a primary key in the customers table and a foreign key in the orders table, providing a surefire way to look-up order data for each customer, if any exists.

-- Return customer order submission details.
SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name,
    oe.customers.cust_email,
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_mode,
	oe.orders.order_status,
	oe.orders.order_total
FROM
	oe.customers
	LEFT JOIN
	oe.orders
		ON oe.customers.customer_id = oe.orders.customer_id;

Real-world Problem 2
A requirement in the first problem was to include all customers in our report, even those not having submitted an order. What if instead, the requirement was to only include those customers not having submitted an order? That’s the case in this problem where you’ve built out a website that accepts orders online. Prior to a customer being able to submit an order, the customer must create an account on your site using an existing email address. This is a common practice and allows businesses to answer the question, “which customers created an account, but never ordered anything?” Of course, answering this question leads to other questions such as “why did these customers never submit an order?” Gathering feedback from those customers could yield valuable information.

Conveniently, meeting this new requirement requires minor modifications to our query in the first problem. The key modification is the addition of the WHERE clause. Recall the result set returned in our previous problem. For those customers not having submitted an order, columns originating from the orders table were not populated. It’s those rows we seek. Additionally, because we know our query’s result set should consist of rows with data not populated in the columns corresponding to an order, we’ll remove order-related columns from our query.

Below is the query and corresponding result set containing the list of customers having not submitted an order. Please note, while I’ve filtered on the customer ID column originating from the orders table, filtering on any non-nullable column within the orders table will have the same effect.

-- Return a list of customers having not submitted an order.
SELECT
	oe.customers.customer_id,
	oe.customers.cust_first_name,
	oe.customers.cust_last_name,
    oe.customers.cust_email
FROM
	oe.customers
	LEFT JOIN
	oe.orders
		ON oe.customers.customer_id = oe.orders.customer_id
WHERE
    oe.orders.customer_id IS NULL;

Knowledge Check
If you’d like to confirm your understanding on JOINS and other fundamental concepts, consider solving a handful of my practice problems here.