尚锦部落资讯网-MySQL多表关联查询

SQL 连接(JOIN) 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准，它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。

不同的 SQL JOIN

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

FULL JOIN：只要其中一个表中存在匹配，则返回行(MySQL不支持FULL JOIN)

实例表1：

mysql> select * from websites;

+----+---------------+---------------------------+-------+---------+

+----+---------------+---------------------------+-------+---------+

| 1 | Google | https://www.google.cm/ | 1 | USA |

| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |

| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |

| 4 | 微博 | http://weibo.com/ | 20 | CN |

| 5 | Facebook | https://www.facebook.com/ | 3 | USA |

| 6 | stackoverflow | http://stackoverflow.com/ | 0 | IND |

| 7 | 百度 | https://www.baidu.com/ | 4 | CN |

+----+---------------+---------------------------+-------+---------+

7 rows in set

实例表2:

mysql> select * from access_log;

+-----+---------+-------+------------+

+-----+---------+-------+------------+

| 1 | 1 | 45 | 2016-05-10 |

| 2 | 3 | 100 | 2016-05-13 |

| 3 | 1 | 230 | 2016-05-14 |

| 4 | 2 | 10 | 2016-05-14 |

| 5 | 5 | 205 | 2016-05-14 |

| 6 | 4 | 13 | 2016-05-15 |

| 7 | 3 | 220 | 2016-05-15 |

| 8 | 5 | 545 | 2016-05-16 |

| 9 | 3 | 201 | 2016-05-17 |

+-----+---------+-------+------------+

9 rows in set

取交集：

mysql> SELECT w.name, a.count, a.date FROM websites w, access_log a WHERE w.id = a.site_id;

+----------+-------+------------+

| name | count | date |

+----------+-------+------------+

| Google | 45 | 2016-05-10 |

| 菜鸟教程 | 100 | 2016-05-13 |

| Google | 230 | 2016-05-14 |

| 淘宝 | 10 | 2016-05-14 |

| Facebook | 205 | 2016-05-14 |

| 微博 | 13 | 2016-05-15 |

| 菜鸟教程 | 220 | 2016-05-15 |

| Facebook | 545 | 2016-05-16 |

| 菜鸟教程 | 201 | 2016-05-17 |

+----------+-------+------------+

9 rows in set

mysql> SELECT w.name, a.count, a.date FROM websites w INNER JOIN access_log a ON w.id = a.site_id;

+----------+-------+------------+

| name | count | date |

+----------+-------+------------+

| Google | 45 | 2016-05-10 |

| 菜鸟教程 | 100 | 2016-05-13 |

| Google | 230 | 2016-05-14 |

| 淘宝 | 10 | 2016-05-14 |

| Facebook | 205 | 2016-05-14 |

| 微博 | 13 | 2016-05-15 |

| 菜鸟教程 | 220 | 2016-05-15 |

| Facebook | 545 | 2016-05-16 |

| 菜鸟教程 | 201 | 2016-05-17 |

+----------+-------+------------+

9 rows in set

注释： INNER JOIN 与 JOIN 是相同的。

取左并集：

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

LEFT JOIN 关键字从左表(table1)返回所有的行，即使右表(table2)中没有匹配。如果右表中没有匹配，则结果为 NULL

mysql> SELECT w.name, a.count, a.date FROM websites w LEFT JOIN access_log a ON w.id = a.site_id;

+---------------+-------+------------+

| name | count | date |

+---------------+-------+------------+

| Google | 45 | 2016-05-10 |

| 菜鸟教程 | 100 | 2016-05-13 |

| Google | 230 | 2016-05-14 |

| 淘宝 | 10 | 2016-05-14 |

| Facebook | 205 | 2016-05-14 |

| 微博 | 13 | 2016-05-15 |

| 菜鸟教程 | 220 | 2016-05-15 |

| Facebook | 545 | 2016-05-16 |

| 菜鸟教程 | 201 | 2016-05-17 |

| stackoverflow | NULL | NULL |

| 百度 | NULL | NULL |

+---------------+-------+------------+

11 rows in set

取右并集：

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

RIGHT JOIN 关键字从右表(table2)返回所有的行，即使左表(table1)中没有匹配。如果左表中没有匹配，则结果为 NULL。

mysql> SELECT Websites.name, access_log.count, access_log.date

FROM access_log

RIGHT JOIN Websites

ON access_log.site_id=Websites.id;

+---------------+-------+------------+

| name | count | date |

+---------------+-------+------------+

| Google | 45 | 2016-05-10 |

| 菜鸟教程 | 100 | 2016-05-13 |

| Google | 230 | 2016-05-14 |

| 淘宝 | 10 | 2016-05-14 |

| Facebook | 205 | 2016-05-14 |

| 微博 | 13 | 2016-05-15 |

| 菜鸟教程 | 220 | 2016-05-15 |

| Facebook | 545 | 2016-05-16 |

| 菜鸟教程 | 201 | 2016-05-17 |

| stackoverflow | NULL | NULL |

| 百度 | NULL | NULL |

+---------------+-------+------------+

11 rows in set

取全集：

MySQL Full Join的实现因为MySQL不支持FULL JOIN,下面是替代方法

left join + union(可去除重复数据)+ right join

select * from A left join B on A.id = B.id (where 条件)union

select * from A right join B on A.id = B.id (where条件);

mysql> SELECT * FROM websites LEFT JOIN access_log ON Websites.id=access_log.site_id

UNION SELECT * FROM websites RIGHT JOIN access_log ON Websites.id=access_log.site_id;

+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+

+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+

| 1 | Google | https://www.google.cm/ | 1 | USA | 1 | 1 | 45 | 2016-05-10 |

| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 2 | 3 | 100 | 2016-05-13 |

| 1 | Google | https://www.google.cm/ | 1 | USA | 3 | 1 | 230 | 2016-05-14 |

| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | 4 | 2 | 10 | 2016-05-14 |

| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 5 | 5 | 205 | 2016-05-14 |

| 4 | 微博 | http://weibo.com/ | 20 | CN | 6 | 4 | 13 | 2016-05-15 |

| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 7 | 3 | 220 | 2016-05-15 |

| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 8 | 5 | 545 | 2016-05-16 |

| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 9 | 3 | 201 | 2016-05-17 |

| 7 | 百度 | https://www.baidu.com/ | 4 | CN | NULL | NULL | NULL | NULL |

+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+

11 rows in set

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。

SQL UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

mysql> SELECT * FROM websites LEFT JOIN access_log ON Websites.id=access_log.site_id

UNION ALL SELECT * FROM websites RIGHT JOIN access_log ON Websites.id=access_log.site_id;