转载自：

 

 /  / MySQL LEFT JOIN

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  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  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  and   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 .

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:

 

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  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 clause 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  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.

Related Tutorials