MySQL LEFT JOIN语句

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

Introducing to MySQL LEFT JOIN

The MySQL LEFT JOIN clause allows you to query data from two or more database tables. The LEFT JOIN clause is an optional part of the SELECT statement, which appears after the FROM clause.

Let’s assume that we are going to query data from two tables T1 and T2. The following is the syntax of the LEFT JOIN clause that joins the two tables:

SELECT T1.c1, T1.c2,... T2.c1,T2.c2 FROM T1 LEFT JOIN T2 ON T1.c1 = T2.c1...

When we join the T1 table  to the T2 table using the LEFT JOIN clause, if a row from the left table T1 matches a row from the right table T2 based on the join condition ( T1.c1 = T2.c1), this row is included in the result set. In case the row in the left table does not match the row in the right table, the row in the left table is also selected and combined with a “fake” row from the right table. The fake row contains NULL values for all corresponding columns in the SELECTclause.

In other words, the LEFT JOIN clause allows you to select rows from the both left and right tables that match, plus all rows from the left table ( T1) even there is no match found for them in the right table ( T2).

The following Venn diagram helps you visualize how the MySQL LEFT JOIN clause works. The intersection between two circles are rows that match in both tables, and the remaining part of the left circle are rows in the T1 table that do not have matches in the T2 table. All rows in the left table are included in the result set.

MySQL LEFT JOIN – Venn Diagram

Notice that the returned rows must also match the condition in the WHERE and HAVING clauses if those clauses are available in the query.

MySQL LEFT JOIN Examples

MySQL LEFT JOIN clause – joining  2 tables example

Let’s take a look at the customers and orders tables.

In the database diagram above:

• Each order in the orders table must belong to a customer in the customers table.

• Each customer in the customers table can have zero or more orders in the orders table.

TO find all orders that belong to each customer, you can use the LEFT JOIN clause as follows:

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

The left table is the customers therefore all customers are included in the result set. However, there are rows in the result set that have customer data but no order data e.g. 168, 169, etc. The order data in these rows are NULL. It means that those customers do not have any order in the orders table.

If you replace the LEFT JOIN clause by the INNER JOIN clause, you only get the customers who have orders in the orders table.

MySQL LEFT JOIN clause to find unmatched rows

The LEFT JOIN clause is very useful when you want to find the rows in the left table that do not match with the rows in the right table. To find the unmatched rows between two tables, you add a WHERE clause to the SELECTstatement to select only rows whose column values in the right table contains the NULL values.

For example, to find all customers who have not ordered any product, you can use the following query:

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

In this tutorial, we have explained the MySQL LEFT JOIN clause and shown you how to apply it to query data from multiple database tables.

Related Tutorials

• Using MySQL SELECT Statement to Query Data

• MySQL INNER JOIN

• MySQL Self Join

原文链接：http://outofmemory.cn/mysql/mysql-left-join