SQL 连接(JOIN)专题

SQL 最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。另外聚集函数也可以在联结中进行使用。

SQL 连接(JOIN) 用于把来自两个或多个表的行结合起来。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

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

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

前提准备

导入语句

-- --------------------------------------------------------
-- 主机:                           10.1.212.135
-- 服务器版本:                        10.6.5-MariaDB - mariadb.org binary distribution
-- 服务器操作系统:                      Win64
-- HeidiSQL 版本:                  11.3.0.6295
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- 导出  表 test.access_log 结构
CREATE TABLE IF NOT EXISTS `access_log` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL DEFAULT 0 COMMENT '网站id',
  `count` int(11) NOT NULL DEFAULT 0 COMMENT '访问次数',
  `date` date NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3;

-- 正在导出表  test.access_log 的数据：~9 rows (大约)
DELETE FROM `access_log`;
/*!40000 ALTER TABLE `access_log` DISABLE KEYS */;
INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`) VALUES
    (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');
/*!40000 ALTER TABLE `access_log` ENABLE KEYS */;

-- 导出  表 test.websites 结构
CREATE TABLE IF NOT EXISTS `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT 0 COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb3;

-- 正在导出表  test.websites 的数据：~7 rows (大约)
DELETE FROM `websites`;
/*!40000 ALTER TABLE `websites` DISABLE KEYS */;
INSERT INTO `websites` (`id`, `name`, `url`, `alexa`, `country`) VALUES
    (1, 'Google', 'https://www.google.cm/', 1, 'USA'),
    (2, '淘宝', 'https://www.taobao.com/', 13, 'CN'),
    (3, '菜鸟学习网', 'http://www.runoob.com/', 5000, 'CN'),
    (4, '微博', 'http://weibo.com/', 20, 'CN'),
    (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA'),
    (6, '百度', 'https://www.baidu.com/', 4, 'CN'),
    (7, 'stackoverflow', 'http://stackoverflow.com/', 0, 'IND');
/*!40000 ALTER TABLE `websites` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

预览数据

MariaDB [test]> 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/    |  5000 | CN      |
|  4 | 微博          | http://weibo.com/         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com/ |     3 | USA     |
|  6 | 百度          | https://www.baidu.com/    |     4 | CN      |
|  7 | stackoverflow | http://stackoverflow.com/ |     0 | IND     |
+----+---------------+---------------------------+-------+---------+
7 rows in set (0.003 sec)


MariaDB [test]> 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 (0.002 sec)

叉联结

笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

有时我们会听到返回称为叉联结（cross join）的笛卡儿积的联结类型。

SQL INNER JOIN 内联结

INNER JOIN 关键字在表中存在匹配时返回行。

语法

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

可省略 INNER 关键字：

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

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

示例：返回所有网站的访问记录

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM Websites
    -> INNER JOIN access_log
    -> ON Websites.id=access_log.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 (0.002 sec)

外联结

SQL LEFT JOIN 语法

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

SQL LEFT JOIN 语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

下面的 SQL 语句将返回所有网站及他们的访问量（如果有的话）。

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM Websites
    -> LEFT JOIN access_log
    -> ON Websites.id=access_log.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 |
| 百度          |  NULL | NULL       |
| stackoverflow |  NULL | NULL       |
+---------------+-------+------------+
11 rows in set (0.002 sec)

注释：LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。

SQL RIGHT JOIN 语法

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

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

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

MariaDB [test]> SELECT Websites.name, access_log.count, access_log.date
    -> FROM  access_log
    -> RIGHT JOIN  websites
    -> ON Websites.id=access_log.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 |
| 百度          |  NULL | NULL       |
| stackoverflow |  NULL | NULL       |
+---------------+-------+------------+
11 rows in set (0.002 sec)

由此我们还能得出结论，表 A 左外连接表 B 等价于表 B 右外连接表 A。

事实上左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个。

SQL FULL OUTER JOIN 全外连接

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

注意：MariaDB、MySQL和SQLite 不支持 FULL OUTER JOIN 语法。你可以在 SQL Server 测试以下实例。

SQL FULL OUTER JOIN 语法

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

多个连接的写法总结

INNER JOIN 连接两个数据表的用法：

SELECT * FROM 表1 
INNER JOIN 表2 ON 表1.字段号=表2.字段号

INNER JOIN 连接三个数据表的用法：

SELECT * FROM 表1
INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 表3 ON 表1.字段号=表3.字段号

自联结

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

自然联结

标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT＊），而对其他表的列使用明确的子集来完成。

事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

联结的性能考虑

注意：性能考虑 DBMS 在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意：

不要联结不必要的表。联结的表越多，性能下降越厉害。
应该总是提供联结条件，否则会得出笛卡儿积。

SELECT cust_name, cust_contact
FROM customers AS c, orders, orderitems AS oi
where c.cust_id = orders.cust_id and orders.order_num = oi.order_num and oi.prod_id = 'RGAN01';

子查询并不总是执行复杂 SELECT 操作的最有效方法，以下语句也可使用联结的相同查询。

多做实验正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

SELECT cust_name, cust_contact FROM customers
where cust_id in (
   select cust_id from orders
   where order_num in (
          select order_num from orderitems where prod_id = 'RGAN01')
);

例题

提问：查找值等于或大于1000的所有订单号和订单数量至少达到这个数的顾客名称。

解答：可以使用使用简单的等联结或ANSI的INNER JOIN语法。

-- Equijoin syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND Orders.order_num = OrderItems.order_num
GROUP BY cust_name HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

-- ANSI INNER JOIN syntax
SELECT cust_name, SUM(item_price*quantity) AS total_price
FROM Customers 
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id 
 INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num
GROUP BY cust_name
HAVING SUM(item_price*quantity) >= 1000
ORDER BY cust_name;

参考

SQL UNION 操作符 | 菜鸟教程
https://www.runoob.com/sql/sql-union.html

SQL必知必会（第5版）-本·福达-微信读书https://weread.qq.com/web/reader/f7632a30720befadf7636bb

最后编辑于：2022.02.07 19:49:51

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,907评论 6赞 506
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,987评论 3赞 395
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,298评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,586评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,633评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,488评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,275评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,176评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,619评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,819评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,932评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,655评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,265评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,871评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,994评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,095评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,884评论 2赞 354

SQL 连接(JOIN)专题

前提准备

叉联结

SQL INNER JOIN 内联结

外联结

SQL LEFT JOIN 语法

SQL RIGHT JOIN 语法

SQL FULL OUTER JOIN 全外连接

多个连接的写法总结

自联结

自然联结

联结的性能考虑

例题

参考

推荐阅读更多精彩内容