转载自：http://www.mysqltutorial.org/mysql-left-join.aspx
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.
Introduction 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 you are going to query data from two tables t1 and t2. The following statement illustrates the syntax of LEFT JOIN clause that joins the two tables:
|
1
2
3
4
5
6
|
SELECT
t1.c1, t1.c2, t2.c1, t2.c2
FROM
t1
LEFT JOIN
t2 ON t1.c1 = t2.c1;
|
When you join the t1 table to the t2 table using the LEFT JOIN clause, if a row from the left table t1matches a row from the right table t2 based on the join condition ( t1.c1 = t2.c1 ), this row will be included in the result set.
In case the row in the left table does not match with 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 for all corresponding columns in the SELECT clause.
In other words, the LEFT JOIN clause allows you to select rows from the both left and right tables that are matched, plus all rows from the left table ( t1 ) even with no matching rows found in the right table ( t2 ).
The following Venn diagram helps you visualize how the 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 any matching row in the t2 table. Hence, 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 conditions in the WHERE and HAVING clauses if those clauses are available in the query.
MySQL LEFT JOIN examples
Using MySQL LEFT JOIN clause to join two tables
Let’s take a look at the customers and orders tables in the sample database.
In the database diagram above:
- Each order in the
orderstable must belong to a customer in thecustomerstable. - Each customer in the
customerstable can have zero or more orders in theorderstable.
To find all orders that belong to each customer, you can use the LEFT JOIN clause as follows:
|
1
2
3
4
5
6
7
8
|
SELECT
c.customerNumber,
c.customerName,
orderNumber,
o.status
FROM
customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
|
The left table is 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 these customers do not have any order in the orders table.
Because we used the same column name ( orderNumber) for joining two tables, we can make the query shorter by using the following syntax:
|
1
2
3
4
5
6
7
8
|
SELECT
c.customerNumber,
customerName,
orderNumber,
status
FROM
customers c
LEFT JOIN orders USING (customerNumber);
|
In this statement, the clause
|
1
|
USING (customerNumber)
|
is equivalent to
|
1
|
ON c.customerNumber = o.customerNumber
|
If you replace the LEFT JOIN clause by the INNER JOIN clause, you get the only customers who have placed at least one order.
Using 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 unmatching rows between two tables, you add a WHEREclause to the SELECT statement to query only rows whose column values in the right table contains the NULL values.
For example, to find all customers who have not placed any order, you use the following query:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT
c.customerNumber,
c.customerName,
orderNumber,
o.status
FROM
customers c
LEFT JOIN
orders o ON c.customerNumber = o.customerNumber
WHERE
orderNumber IS NULL;
|
Condition in WHERE clause vs. ON clause
See the following example.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT
o.orderNumber,
customerNumber,
productCode
FROM
orders o
LEFT JOIN
orderDetails USING (orderNumber)
WHERE
orderNumber = 10123;
|
In this example, we used the LEFT JOIN clause to query data from the orders and orderDetails tables. The query returns an order and its detail, if any, for the order 10123.
However, if you move the condition from the WHERE clause to the ON clause:
|
1
2
3
4
5
6
7
8
9
|
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 but only the order 10123 will have detail associated with it as shown below.
Notice that for INNER JOIN clause, the condition in the ON clause is equivalent to the condition in the WHERE clause.
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.