» Basic MySQL Tutorial » How to Use MySQL LEFT JOIN to Select Data from Multiple Tables

MySQL LEFT JOIN

Summary: In this tutorial, you will learn how to use MySQL LEFT JOIN clause to retrieve data from more than one table.

Introducing to MySQL LEFT JOIN

The MySQL LEFT JOIN clause is an optional element of the SQL SELECT statement to allow you to retrieve data from additional tables. The MySQL LEFT JOIN clause consists of LEFT JOIN keyword followed by the second table you want to join. Next element is the ON keyword followed by the join condition. In the join condition, you specify the columns to be used for matching row in the two tables. The syntax of MySQL is as follows:

SELECT t1.c1, t1.c2,…t2.c1,t2.c2
FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1 …(join_condition)
WHERE where_condition

The MySQL LEFT JOIN clause works like this: when a row from the left table matches a row from the right table based on join_condition, the row’s content are selected as an output row. When row in the left table has no match it is still selected for output, but combined with a “fake” row from the right table that contains NULL in all columns. In short, the MySQL LEFT JOIN clause allows you to select all rows from the left table even there is no match for them in the right table.

Example of MySQL LEFT JOIN

Let’s take a look at two table customers and orders: If you want to know which customer has which order and each order’s status. You can use the MySQL LEFT JOIN as follows:

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

MySQL LEFT JOIN

There are more rows which are not listed on this screenshot.

The left table is the customers table so you see all customers are listed as the way MySQL LEFT JOIN clause works. However, there are rows that we have customer information but all order information are NULL. This means those customers do not have any order in our database. The MySQL LEFT JOIN clause is very useful when you want to find the records in the left table that are unmatched by the right table. You can accomplish this by add a WHERE clause to select only that have NULL values in a right table column. So to find all customers who does not have any order in our database we can use the MySQL LEFT JOIN clause as follows:

SELECT c.customerNumber, customerName,orderNUmber, o.status
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE orderNumber is NULL

MySQL LEFT JOIN - Unmatched Records

There are more rows which are not listed on this screenshot.

As the result, the query only returns customers which do not have any order represented by NULL values.

In this tutorial, you have learned how to use MySQL LEFT JOIN clause to select data from multiple tables. You've also learned how to use MySQL LEFT JOIN to find unmatched records between two tables.

Search