MySQL中各种join

(本文中SQL语句均在Mysql v5.1中验证)
注0：在实验中，MySQL v5.1不支持full outer join，下文中全外连接部分SQL输出的结果是本人根据其含义自行输出的。




连接类型： inner join
left outer join
right outer join
full outer join

连接条件： natural
on <谓词>
using (A1, A2, ..., An)





mysql> select * from loan;
+-------------+-------------+--------+
| loan_number | branch_name | amount |
+-------------+-------------+--------+
| L-170       | Downtonw    | 3000   |
| L-230       | Redwood     | 4000   |
| L-260       | Perryidge   | 1700   |
+-------------+-------------+--------+
3 rows in set (0.02 sec)


mysql> select * from borrower;
+---------------+-------------+
| customer_name | loan_number |
+---------------+-------------+
| Jones         | L-170       |
| Smith         | L-230       |
| Hayes         | L-155       |
+---------------+-------------+
3 rows in set (0.02 sec)








内连接: inner join  （对于内连接而言，连接条件是可选的）
1) 连接类型: (inner) join + 连接条件: 无   =>   笛卡尔乘积
mysql> select * from loan inner join borrower;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-260       | Perryidge   | 1700   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-260       | Perryidge   | 1700   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
| L-260       | Perryidge   | 1700   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
9 rows in set (0.02 sec)




2) 连接类型: (inner) join + 连接条件: natural   =>   自然连接(去掉所有公共属性)
mysql> select * from loan natural join borrower;
+-------------+-------------+--------+---------------+
| loan_number | branch_name | amount | customer_name |
+-------------+-------------+--------+---------------+
| L-170       | Downtonw    | 3000   | Jones         |
| L-230       | Redwood     | 4000   | Smith         |
+-------------+-------------+--------+---------------+
2 rows in set (0.02 sec)




3) 连接类型: (inner) join + 连接条件: on   =>   θ连接
mysql> select * from loan inner join borrower on loan.loan_number=borrower.loan_number;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
+-------------+-------------+--------+---------------+-------------+
2 rows in set (0.02 sec)


mysql> select * from loan inner join borrower on amount > 2000;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
6 rows in set (0.01 sec)




4) 连接类型: (inner) join + 连接条件: using   =>   可选择连接属性的自然连接(去掉所有进行连接的属性)
mysql> select * from loan inner join borrower using (loan_number);
+-------------+-------------+--------+---------------+
| loan_number | branch_name | amount | customer_name |
+-------------+-------------+--------+---------------+
| L-170       | Downtonw    | 3000   | Jones         |
| L-230       | Redwood     | 4000   | Smith         |
+-------------+-------------+--------+---------------+
2 rows in set (0.01 sec)




注1：MySQL的内连接中natural和on不能连用
mysql>  select * from loan natural join borrower on amount>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'on amount>2000' at line 1








外连接: outer join  （对于外连接而言，连接条件是必须的）
1. 左外连接: left (outer) join
1) 连接类型: left (outer) join + 连接条件: 无   =>   报错
mysql> select * from loan left outer join borrower;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near '' at line 1




2) 连接类型: left (outer) join + 连接条件: natural   =>   先自然连接(去掉所有公共属性)，
再把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中。
mysql> select * from loan natural left outer join borrower;
+-------------+-------------+--------+---------------+
| loan_number | branch_name | amount | customer_name |
+-------------+-------------+--------+---------------+
| L-170       | Downtonw    | 3000   | Jones         |
| L-230       | Redwood     | 4000   | Smith         |
| L-260       | Perryidge   | 1700   |               |
+-------------+-------------+--------+---------------+
3 rows in set (0.01 sec)




3) 连接类型: left (outer) join + 连接条件: on   =>   先做θ连接（即先对两表做内连接（笛卡尔乘积），
再用on后的条件筛选出满足条件的行），最后把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中。
mysql> select * from loan left outer join borrower on loan.loan_number=borrower.loan_number;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-260       | Perryidge   | 1700   |               |             |
+-------------+-------------+--------+---------------+-------------+
3 rows in set (0.01 sec)


mysql> select * from loan left outer join borrower on amount>2000;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
| L-260       | Perryidge   | 1700   |               |             |
+-------------+-------------+--------+---------------+-------------+
7 rows in set (0.01 sec)




4) 连接类型: left (outer) join + 连接条件: using   =>   根据using后的属性做自然连接(去掉所有进行连接的属性)，
最后把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中。
mysql> select * from loan left outer join borrower using (loan_number);
+-------------+-------------+--------+---------------+
| loan_number | branch_name | amount | customer_name |
+-------------+-------------+--------+---------------+
| L-170       | Downtonw    | 3000   | Jones         |
| L-230       | Redwood     | 4000   | Smith         |
| L-260       | Perryidge   | 1700   |               |
+-------------+-------------+--------+---------------+
3 rows in set (0.02 sec)




注2：MySQL的左外连接中natural和on不能连用
mysql>  select * from loan natural left outer join borrower on amount>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'on amount>2000' at line 1






2. 右外连接: right (outer) join
1) 连接类型: right (outer) join + 连接条件: 无   =>   报错
mysql> select * from loan right outer join borrower;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near '' at line 1




2) 连接类型: right (outer) join + 连接条件: natural   =>    先自然连接(去掉所有公共属性)，
再把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
mysql> select * from loan natural right outer join borrower;
+-------------+---------------+-------------+--------+
| loan_number | customer_name | branch_name | amount |
+-------------+---------------+-------------+--------+
| L-170       | Jones         | Downtonw    | 3000   |
| L-230       | Smith         | Redwood     | 4000   |
| L-155       | Hayes         |             | NULL   |
+-------------+---------------+-------------+--------+
3 rows in set (0.01 sec)




3) 连接类型: right (outer) join + 连接条件: on   =>   先做θ连接（即先对两表做内连接（笛卡尔乘积），
再用on后的条件筛选出满足条件的行），最后把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
mysql> select * from loan right outer join borrower on loan.loan_number=borrower.loan_number;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
|             |             | NULL   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
3 rows in set (0.02 sec)


mysql> select * from loan right outer join borrower on amount>2000;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
6 rows in set (0.01 sec)




4) 连接类型: right (outer) join + 连接条件: using   =>   根据using后的属性做自然连接(去掉所有进行连接的属性)，
最后把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
mysql> select * from loan right outer join borrower using (loan_number);
+-------------+---------------+-------------+--------+
| loan_number | customer_name | branch_name | amount |
+-------------+---------------+-------------+--------+
| L-170       | Jones         | Downtonw    | 3000   |
| L-230       | Smith         | Redwood     | 4000   |
| L-155       | Hayes         |             | NULL   |
+-------------+---------------+-------------+--------+
3 rows in set (0.00 sec)




注3：MySQL的右外连接中natural和on不能连用
mysql>  select * from loan natural right outer join borrower on amount>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'on amount>2000' at line 1






3. 全外连接: full (outer) join
1) 连接类型: full (outer) join + 连接条件: natural   =>   先自然连接(去掉所有公共属性)，
再把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中，
以及把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
select * from loan natural full outer join borrower;
+-------------+-------------+----------+---------------+
| loan_number | branch_name | amount   | customer_name |
+-------------+-------------+----------+---------------+
| L-170       | Downtonw    | 3000     | Jones         |
| L-230       | Redwood     | 4000     | Smith         |
| L-260       | Perryidge   | 1700     |               |
| L-155       |             | NULL     | Hayes         |
+-------------+-------------+----------+---------------+




2) 连接类型: full (outer) join + 连接条件: on   =>   先做θ连接（即先对两表做内连接（笛卡尔乘积），
再用on后的条件筛选出满足条件的行），最后
再把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中，
以及把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
select * from loan full outer join borrower on loan.loan_number=borrower.loan_number;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-260       | Perryidge   | 1700   |               |             |
|             |             | NULL   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+




3) 连接类型: full (outer) join + 连接条件: using   =>   根据using后的属性做自然连接(去掉所有进行连接的属性)，
再把左表中在结果集中未出现的行（即该行在右表中无匹配）加到结果集中，
以及把右表中在结果集中未出现的行（即该行在左表中无匹配）加到结果集中。
select * from loan full outer join borrower using (loan_number);
+-------------+-------------+----------+---------------+
| loan_number | branch_name | amount   | customer_name |
+-------------+-------------+----------+---------------+
| L-170       | Downtonw    | 3000     | Jones         |
| L-230       | Redwood     | 4000     | Smith         |
| L-260       | Perryidge   | 1700     |               |
| L-155       |             | NULL     | Hayes         |
+-------------+-------------+----------+---------------+










最后，数据库中还有一种cross join，它等价于(inner) join
mysql> select * from loan cross join borrower;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-260       | Perryidge   | 1700   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-260       | Perryidge   | 1700   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
| L-260       | Perryidge   | 1700   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
9 rows in set (0.02 sec)


mysql> select * from loan natural cross join borrower;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r
ight syntax to use near 'cross join borrower' at line 1


mysql> select * from loan cross join borrower on amount>2000;
+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| L-170       | Downtonw    | 3000   | Jones         | L-170       |
| L-230       | Redwood     | 4000   | Jones         | L-170       |
| L-170       | Downtonw    | 3000   | Smith         | L-230       |
| L-230       | Redwood     | 4000   | Smith         | L-230       |
| L-170       | Downtonw    | 3000   | Hayes         | L-155       |
| L-230       | Redwood     | 4000   | Hayes         | L-155       |
+-------------+-------------+--------+---------------+-------------+
6 rows in set (0.02 sec)


mysql> select * from loan cross join borrower using (loan_number);
+-------------+-------------+--------+---------------+
| loan_number | branch_name | amount | customer_name |
+-------------+-------------+--------+---------------+
| L-170       | Downtonw    | 3000   | Jones         |
| L-230       | Redwood     | 4000   | Smith         |
+-------------+-------------+--------+---------------+
2 rows in set (0.01 sec)