MySQL LEFT JOIN

Summary: in this tutorial, you will learn about MySQL LEFT JOIN clause and how to use it to retrieve data from two or more related tables.

Introduction to MySQL LEFT JOIN clause

The LEFT JOIN allows you to retrieve data from two or more tables. Like the INNER JOIN clause, the LEFT JOIN is an optional clause of the SELECT statement, which appears immediately after the FROM clause.

The following statement illustrates how to use the LEFT JOIN clause to join the two tables, t1 and t2:

SELECT 
    select_list
FROM
    t1
LEFT JOIN t2 ON 
    join_condition;Code language: SQL (Structured Query Language) (sql)

When you use the LEFT JOIN clause, the concepts of the left table (t1) and the right table (t2) come into play within the syntax.

The LEFT JOIN clause selects data starting from the left table (t1), matching each row from the left table (t1) with every corresponding row from the right table(t2) based on the join_condition.

If the rows from both tables satisfy the join condition, the left join combines columns from both tables into a new row and includes this new row in the result rows.

If a row from the left table (t1) does not match with any row from the right table(t2), the left join still combines columns of rows from both tables into a new row and includes the new row in the result set. However, it fills in the columns of the row from the right table with the NULL values.

In essence, the LEFT JOIN returns all rows from the left table, irrespective of whether a matching row from the right table exists or not.

In the absence of a match, the columns of the row from the right table will be filled with NULL values.

The following Venn diagram helps you visualize how the LEFT JOIN clause works:

MySQL LEFT JOIN clause examples

Let’s take some examples of using the LEFT JOIN clause.

1) Using MySQL LEFT JOIN clause to join two tables

See the following tables customers and orders in the sample database.

Each customer can have zero or more orders, whereas each order must belong to one customer.

The following query uses the LEFT JOIN clause to find all customers and their corresponding orders:

SELECT 
    customers.customerNumber, 
    customerName, 
    orderNumber, 
    status
FROM
    customers
LEFT JOIN orders ON 
    orders.customerNumber = customers.customerNumber;Code language: SQL (Structured Query Language) (sql)

Alternatively, you can make the query shorter using table aliases:

SELECT
    c.customerNumber,
    customerName,
    orderNumber,
    status
FROM
    customers c
LEFT JOIN orders o 
    ON c.customerNumber = o.customerNumber;Code language: SQL (Structured Query Language) (sql)

In this example:

• The customers is the left table and orders is the right table.
• The LEFT JOIN clause returns all customers including the customers who have no order. If a customer has no order, the values in the column orderNumber and status are NULL.

Since both the customers and orders tables share the same column name ( customerNumber) in the join condition using the equal operator, you can utilize the USING syntax as follows:

SELECT
	customerNumber,
	customerName,
	orderNumber,
	status
FROM
	customers
LEFT JOIN orders USING (customerNumber);Code language: SQL (Structured Query Language) (sql)

The following clauses are equivalent:

USING (customerNumber)Code language: SQL (Structured Query Language) (sql)

And

ON c.customerNumber = o.customerNumberCode language: SQL (Structured Query Language) (sql)

If you replace the LEFT JOIN clause by the INNER JOIN clause, you will get the only customers who have at least one order.

2) Using MySQL LEFT JOIN clause to find unmatched rows

The LEFT JOIN clause is very useful when you need to identify rows in a table that doesn’t have a matching row from another table.

The following example uses the LEFT JOIN to find customers without any orders:

SELECT 
    c.customerNumber, 
    c.customerName, 
    o.orderNumber, 
    o.status
FROM
    customers c
LEFT JOIN orders o 
    ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;Code language: SQL (Structured Query Language) (sql)

3) Using MySQL LEFT JOIN to join three tables

See the following three tables employees, customers, and payments:

This example uses two LEFT JOIN clauses to join the three tables: employees, customers, and payments.

SELECT 
    lastName, 
    firstName, 
    customerName, 
    checkNumber, 
    amount
FROM
    employees
LEFT JOIN customers ON 
    employeeNumber = salesRepEmployeeNumber
LEFT JOIN payments ON 
    payments.customerNumber = customers.customerNumber
ORDER BY 
    customerName, 
    checkNumber;Code language: SQL (Structured Query Language) (sql)

This picture shows the partial output:

How it works.

• The first LEFT JOIN returns all employees and customers who represented each employee or NULL if the employee is not in charge of any customer.
• The second LEFT JOIN retrieve payments for each customer represented by an employee or returns NULL if the customer has no payments.

Condition in WHERE clause vs. ON clause

The following example uses the LEFT JOIN clause to query data from the orders and  orderDetails tables:

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
LEFT JOIN orderDetails 
    USING (orderNumber)
WHERE
    orderNumber = 10123;Code language: SQL (Structured Query Language) (sql)

The query returns the order and its line items of the order number 10123.

However, if you move the condition from the WHERE clause to the ON clause:

SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders o
LEFT JOIN orderDetails d 
    ON o.orderNumber = d.orderNumber AND 
       o.orderNumber = 10123;Code language: SQL (Structured Query Language) (sql)

It will have a different meaning.

In this case, the query returns all orders; However, only the order 10123 will have associated line items as shown in the query result:

Notice that for INNER JOIN clause, the condition in the ON clause is equivalent to the condition in the WHERE clause.

In this tutorial, you have learned how to use the MySQL LEFT JOIN clause to join data from two or more tables.