A2-02-12.DML-MySQL Join Made Easy For Beginners
转载自：http://www.mysqltutorial.org/mysql-join/
MySQL Join Made Easy For Beginners
Summary: in this tutorial, you will learn various MySQL join statements to query data from multiple tables.
Introduction to MySQL join statements
A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns. Because of this, data in each table is incomplete from the business perspective.
For example, in the sample database, we have the orders and orderdetails tables that are linked using the orderNumber column.
To get complete orders’ data, you need to query data from both orders and orderdetails table.
And that’s why MySQL JOIN comes into the play.
A MySQL join is a method of linking data from one (self-join) or more tables based on values of the common column between tables.
MySQL supports the following types of joins:
To join tables, you use the CROSS JOIN, INNER JOIN, LEFT JOIN or RIGHT JOIN clause for the corresponding type of join. The join clause is used in the SELECT statement appeared after the FROMclause.
Notice that MySQL does not support full outer join.
To make easy for you to understand each type of join, we will use the t1 and t2 tables with the following structures:
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE t1 (
id INT PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
CREATE TABLE t2 (
id VARCHAR(50) PRIMARY KEY,
pattern VARCHAR(50) NOT NULL
);
|
Both t1 and t2 tables have the pattern column, which is also the common column between tables.
The following statements insert data into both t1 and t2 tables:
|
1
2
3
4
5
6
7
8
9
|
INSERT INTO t1(id, pattern)
VALUES(1,'Divot'),
(2,'Brick'),
(3,'Grid');
INSERT INTO t2(id, pattern)
VALUES('A','Brick'),
('B','Grid'),
('C','Diamond');
|
And the pictures below illustrate data from both t1 and t2 tables:
MySQL CROSS JOIN
The CROSS JOIN makes a Cartesian product of rows from multiple tables. Suppose, you join t1 and t2tables using the CROSS JOIN, the result set will include the combinations of rows from the t1 table with the rows in the t2 table.
To perform cross join, you use the CROSS JOIN clause as in the following statement:
|
1
2
3
4
5
|
SELECT
t1.id, t2.id
FROM
t1
CROSS JOIN t2;
|
The following shows the result set of the query:
As you can see, each row in the t1 table combines with rows in the t2 table to form the Cartesian product.
The following picture illustrates the CROSS JOIN between t1 and t2 tables.
MySQL INNER JOIN
To form anINNER JOIN, you need a condition which is known as a join-predicate. An INNER JOIN requires rows in the two joined tables to have matching column values. The INNER JOIN creates the result set by combining column values of two joined tables based on the join-predicate.
To join two tables, the INNER JOIN compares each row in the first table with each row in the second table to find pairs of rows that satisfy the join-predicate. Whenever the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of the two tables are included in the result set.
The following statement uses the INNER JOIN clause to join t1 and t2 tables:
|
1
2
3
4
5
6
|
SELECT
t1.id, t2.id
FROM
t1
INNER JOIN
t2 ON t1.pattern = t2.pattern;
|
In this statement, the following expression is the join-predicate:
|
1
|
t1.pattern = t2.pattern
|
It means that rows in t1 and t2 tables must have the same values in the pattern column to be included in the result.
The following illustrates the result of the query:
The following picture illustrates the INNER JOIN between t1 and t2 tables:
In this illustration, the rows in both tables must have the same pattern to be included in the result set.
MySQL LEFT JOIN
Similar to an INNER JOIN, a LEFT JOIN also requires a join-predicate. When joining two tables using a LEFT JOIN, the concepts of left table and right table are introduced.
Unlike an INNER JOIN, a LEFT JOIN returns all rows in the left table including rows that satisfy join-predicate and rows do not. For the rows that do not match the join-predicate, NULLs appear in the columns of the right table in the result set.
The following statement uses the LEFT JOIN clause to join t1 and t2 tables:
|
1
2
3
4
5
6
7
|
SELECT
t1.id, t2.id
FROM
t1
LEFT JOIN
t2 ON t1.pattern = t2.pattern
ORDER BY t1.id;
|
As you can see, all rows in the t1 table are included in the result set. For the rows in the t1 table (left table) that do not have any matching row in the t2 table (right table), NULLs are used for columns in t2 table.
The following picture illustrates the LEFT JOIN between t1 and t2 tables:
In this illustration, the following rows share the same pattern: (2 and A), (3 and B). The row with id 1 in the t1 table has no matching row in the t2 table, therefore, NULL are used for columns of the t2 table in the result set.
MySQL RIGHT JOIN
A RIGHT JOIN is similar to the LEFT JOIN except that the treatment of tables is reversed. With a RIGHT JOIN, every row from the right table ( t2) will appear in the result set. For the rows in the right table that do not have the matching rows in the left table ( t1), NULLs appear for columns in the left table ( t1).
The following statement joins t1 and t2 tables using RIGHT JOIN:
|
1
2
3
4
5
6
7
|
SELECT
t1.id, t2.id
FROM
t1
RIGHT JOIN
t2 on t1.pattern = t2.pattern
ORDER BY t2.id;
|
In this result, all rows from the right table ( t2) appear in the result set. For the rows in the right table ( t2) that have no matching rows in the left table ( t1), NULL appears for columns of the left table ( t1).
The following picture illustrates the RIGHT JOIN between t1 and t2 tables:
In this tutorial, you have learned various MySQL join statements including cross join, inner join, left join and right join to query data from two or more tables.
