大家好,又见面了,我是你们的朋友全栈君。
SQL 连接(JOIN) 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。左连接与右连接的左右指的是以两张表中的哪一张为基准,它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行,用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接,左表是基准表,左表的所有行都出现在结果中,右表则可能因为无法与基准表匹配而出现是空值的字段。
不同的 SQL JOIN
在我们继续讲解实例之前,我们先列出您可以使用的不同的 SQL JOIN 类型:
INNER JOIN:如果表中有至少一个匹配,则返回行
- LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN:只要其中一个表中存在匹配,则返回行(MySQL不支持FULL JOIN)
mysql> select * from websites;
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 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;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 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 是相同的。
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
取右并集:
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;
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name | url | alexa | country | aid | site_id | count | date |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| 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 |
| 6 | stackoverflow | http://stackoverflow.com/ | 0 | IND | NULL | NULL | NULL | NULL |
| 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 语法
column_name(s) FROM
table1
UNION
SELECT
column_name(s) FROM
table2;
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
SQL UNION ALL 语法
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;
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name | url | alexa | country | aid | site_id | count | date |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
| 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 |
| 6 | stackoverflow | http://stackoverflow.com/ | 0 | IND | NULL | NULL | NULL | NULL |
| 7 | 百度 | https://www.baidu.com/ | 4 | CN | NULL | NULL | NULL | NULL |
| 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 |
+----+---------------+---------------------------+-------+---------+------+---------+-------+------------+
20 rows in set
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/137897.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...