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
FROMclause. - Second, the table that you want to join with the main table, which appears in the
INNER JOINclause. 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
ONkeyword of theINNER JOINclause. 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
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 ccolumn 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
productCodeandproductNamefrom theproductstable. - The
textDescriptionof product lines from theproductlinestable.
To do this, you need to select data from both tables by matching rows based on the productlinecolumns 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 BYclause 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_1678that 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.
