A2-02-13.DML- MySQL INNER JOIN
转载自:http://www.mysqltutorial.org/mysql-inner-join.aspx
MySQL INNER JOIN
Summary: in this tutorial, you will learn how to use MySQL INNER JOIN clause to select data from multiple tables based on join conditions.
Introducing MySQL INNER JOIN
clause
The MySQL INNER JOIN
clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.
The INNER JOIN
clause is an optional part of the SELECT
statement. It appears immediately after the FROM
clause.
Before using the INNER JOIN
clause, you have to specify the following criteria:
- First, the main table that appears in the
FROM
clause. - Second, the table that you want to join with the main table, which appears in the
INNER JOIN
clause. In theory, you can join a table with many other tables. However, for a better performance, you should limit the number of tables to join. - Third, the join condition or join predicate. The join condition appears after the
ON
keyword of theINNER JOIN
clause. The join condition is the rule for matching rows in the main table with the rows in the other tables.
The syntax of the INNER JOIN
clause is as follows:
1
2
3
4
5
6
|
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE where_conditions;
|
Let’s simplify the syntax above by assuming that we are joining two tables t1
and t2
using the INNER JOIN
clause.
1
2
3
|
SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition;
|
For each row in the t1
table, the INNER JOIN
clause compares it with each row of the t2
table to check if both of them satisfy the join condition. When the join condition is met, the INNER JOIN
will return a new row which consists of columns in both t1
and t2
tables.
Notice that the rows in both t1
and t2
tables have to be matched based on the join condition. If no match found, the query will return an empty result set. This logic is also applied when you join more than 2 tables.
The following Venn diagram illustrates how the INNER JOIN
clause works. The rows in the result set must appear in both tables: t1
and t2
as shown in the intersection part of two circles.
![MySQL INNER JOIN Venn Diagram MySQL INNER JOIN Venn Diagram](http://www.mysqltutorial.org/wp-content/uploads/2011/05/MySQL-INNER-JOIN-Venn-Diagram-300x166.png)
Avoid ambiguous column error in MySQL INNER JOIN
If you join multiple tables that have the same column name, you have to use table qualifier to refer to that column in the SELECT
and ON
clauses to avoid the ambiguous column error.
For example, if both t1
and t2
tables have the same column named c
, you have to refer to the c
column using the table qualifiers as t1.c
or t2.c
in the SELECT
and ON
clauses.
To save time typing the table qualifiers, you can use table aliases in the query. For example, you can give the verylongtablename
table a table’s alias t
and refer to its columns using t.column
instead of using the verylongtablename.column
MySQL INNER JOIN
examples
Let’s look at the products
and productlines
tables in the sample database.
In this diagram, the products
table has the productLine
column referenced to the productline
column of the productlines
table. The productLine
column in the products
table is called a foreign key column.
Typically, you join tables that have foreign key relationships like the productlines
and products
tables.
Now, if you want to get
- The
productCode
andproductName
from theproducts
table. - The
textDescription
of product lines from theproductlines
table.
To do this, you need to select data from both tables by matching rows based on the productline
columns using the INNER JOIN
clause as follows:
1
2
3
4
5
6
7
8
|
SELECT
productCode,
productName,
textDescription
FROM
products t1
INNER JOIN
productlines t2 ON t1.productline = t2.productline;
|
Because the joined columns of both tables have the same name productline
, you can use the following syntax:
1
2
3
4
5
6
7
8
|
SELECT
productCode,
productName,
textDescription
FROM
products
INNER JOIN
productlines USING (productline);
|
It returns the same result set however with this syntax you don’t have to use the table aliases.
MySQL INNER JOIN with GROUP BY clause
See the following orders
and orderdetails
tables.
You can get the order number, order status and total sales from the orders
and orderdetails
tables using the INNER JOIN
clause with the GROUP BY
clause as follows:
1
2
3
4
5
6
7
8
9
|
SELECT
T1.orderNumber,
status,
SUM(quantityOrdered * priceEach) total
FROM
orders AS T1
INNER JOIN
orderdetails AS T2 ON T1.orderNumber = T2.orderNumber
GROUP BY orderNumber;
|
Similarly, the following query is equivalent to the one above:
1
2
3
4
5
6
7
8
9
|
SELECT
orderNumber,
status,
SUM(quantityOrdered * priceEach) total
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
GROUP BY orderNumber;
|
MySQL INNER JOIN
using operator other than equal
So far, you have seen that the join predicate used the equal operator (=) for matching rows. In addition, you can use other operators such as greater than ( >
), less than ( <
), and not-equal ( <>
) operator to form the join predicates.
The following query uses a less-than ( <
) join to find sales prices of the product whose code is S10_1678
that are less than the manufacturer’s suggested retail price (MSRP) for that product.
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
orderNumber,
productName,
msrp,
priceEach
FROM
products p
INNER JOIN
orderdetails o ON p.productcode = o.productcode
AND p.msrp > o.priceEach
WHERE
p.productcode = 'S10_1678';
|
In this tutorial, you have learned how to use the MySQL INNER JOIN
to query data from multiple tables.