LEFT JOIN

Return all rows from the left table and matching rows from the right table or null if no matching rows are found in the right table.

Here’s a LEFT JOIN rewrite based on your query, followed by an explanation:

SELECT
    Table1.column1,
    Table2.column2
FROM
    Table1
LEFT JOIN Table2 
ON Table1.common_column = Table2.common_column
WHERE
    Table1.some_column = 'some_value'
ORDER BY
    Table1.column1 DESC;

Explanation of the LEFT JOIN:

1. FROM Table1: This specifies that Table1 is the main or "left" table from which we will retrieve data.
2. LEFT JOIN Table2 ON Table1.common_column = Table2.common_column: This part joins Table1 with Table2 using the common_column. The LEFT JOIN ensures:
• All rows from Table1 are included in the result set.
• If a row in Table1 doesn’t have a matching row in Table2, the Table2 columns in that row are filled with NULL values.
• If a match is found, data from both tables is combined into a single row.
3. WHERE Table1.some_column = 'some_value': This condition filters the rows from Table1 based on a specific value in Table1.some_column. Only rows from Table1 that meet this condition will appear in the results.
4. ORDER BY Table1.column1 DESC: This sorts the final results in descending order by Table1.column1.

Visual Summary of LEFT JOIN:

Imagine two tables (Table1 on the left and Table2 on the right). The LEFT JOIN:

• Includes all rows from the left table (Table1).
• Combines data from Table2 if there’s a match.
• Fills Table2 columns with NULL if there’s no match for a Table1 row.

In contrast, an INNER JOIN would only include rows where there’s a match between Table1 and Table2, excluding rows in Table1 without a match.

This behavior makes LEFT JOINs helpful when you need complete data from one table (like Table1) and any matching data from another (Table2).

MySQL LEFT JOIN clause examples

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

Question

Given two tables, customers and orders, in the sample database, write a query to retrieve all customers along with any orders they have placed.

• The customers table includes columns customerNumber and customerName.
• The orders table includes columns orderNumber, status, and customerNumber.
• Each customer may have zero or more orders, but each order belongs to only one customer.

Task

Write a query to return:

• customerNumber and customerName from the customers table.
• orderNumber and status from the orders table.

Requirements

1. Use a LEFT JOIN to include all customers, whether or not they have orders.
2. Simplify the query by using table aliases for readability.

SELECT
    c.customerNumber,
    customerName,
    orderNumber,
    status
FROM
    customers AS c
LEFT JOIN orders AS o 
    ON c.customerNumber = o.customerNumber;

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.

Question 7

The LEFT JOIN clause is particularly useful for identifying rows in one table that do not have matching rows in another table. This task demonstrates how to use LEFT JOIN to find customers who have not placed any orders.

• The customers table includes columns customerNumber and customerName.
• The orders table includes columns orderNumber, status, and customerNumber.

Task

Write a query to return:

• customerNumber and customerName from the customers table.
• orderNumber and status from the orders table.

Requirements

1. Use a LEFT JOIN to include all customers, even if they do not have any associated orders.
2. Add a condition to filter out customers with orders, showing only those who have not placed 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;

Using MySQL LEFT JOIN to join three tables

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

This task demonstrates how to use multiple LEFT JOIN clauses to join three tables: employees, customers, and payments.

• The employees table includes columns employeeNumber, lastName, and firstName.
• The customers table includes columns customerNumber, customerName, and salesRepEmployeeNumber (which references employeeNumber).
• The payments table includes columns customerNumber, checkNumber, and amount.

Task

Write a query to return:

• lastName and firstName from the employees table.
• customerName from the customers table.
• checkNumber and amount from the payments table.

Requirements

1. Use two LEFT JOIN clauses to join the employees, customers, and payments tables.
2. Order the results by customerName and checkNumber for clarity.

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;

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;

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;

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.