MySQL使用join连接多表查询：内连接、自连接、自然连接、外连接查询、附笛卡尔积

本文用到的样例表1

• vendors表：存储销售产品的供应商。供应商ID（vend_id）列用来匹配产品和供应商。

CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

• products表：包含产品目录，每行一个产品。每个产品有唯一的ID（prod_id），通过vend_id关联到它的供应商。
  • prod_id为主键。
  • vend_id为外键，关联到vendors的vend_id。

CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

• customers表：存储所有顾客的信息。每个顾客有唯一的ID（cust_id）。

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

• orders表：存储顾客订单（但不是订单详细）。每个订单有唯一的编号（order_num）。
  • order_num为主键，且为自动增量字段。
  • cust_id为外键，关联到customers的cust_id。

CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

• orderitems表：存储每个订单中的实际物品，每个订单的每个物品占一行。orders表中的每一行，对应orderitems表中的一行或多行、每个订单物品由订单号加订单物品作为唯一标识（主键）。
  • order_num和order_item组合作为主键。
  • order_num为外键，关联到orders的order_num。
  • prod_id为外键，关联到products的prod_id。

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

• 上面这些样例表的定义及数据的相关sql请参阅https://github.com/dongyusheng/csdn-code/tree/master/mysql/order，下载之后导入数据库即可：

本文用到的样例表2

• 建立如下两个表t1和t2：

CREATE TABLE t1
(
  i1  INT NOT NULL,
  ci  VARCHAR(2) NOT NULL
);
CREATE TABLE t2
(
  i2  INT NOT NULL,
  c2  VARCHAR(2) NOT NULL
);

• 然后插入如下的数据：

INSERT INTO t1 VALUES(1, 'a'),(2, 'b'),(3, 'c');
INSERT INTO t2 VALUES(2, 'c'),(3, 'b'),(4, 'a');

• 最终结果如下所示：

SELECT * FROM t1;
SELECT * FROM t2;

本文用到的样例表3

• fruits表：存储水果信息。
• supplier表：存储供应商信息。
• customers表：顾客表。
• orders表：订单表。

一、连接查询的概述

• 概念：在关系数据库管理系统中，当查询数据时，通过连接运算符查询出存放在多个表中的不同实体信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。
• 备注：连接有时也称为“联结”。
• 分类：
  • 内连接。
  • 外连接。
• 等值连接查询：表之间的查询连接条件为相等（=）。
• 不等值连接查询：表之间的查询连接条件不是相等（=）即为不等值连接查询。

为什么要使用连接？

• 分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。
• 如果数据存储在多个表中，怎样用单条select语句检索出数据哪？答案就是使用连接。简单地说，连接是一种机制，用来在一条select语句中关联表，因此称之为连接。使用特殊的语法，可以连接多个表返回一组输出，连接在运行时关联表中正确的行。

连接使用的一些要点

• 注意所使用的连接类型，一般我们使用内部连接，但是用外部连接也是有效的。
• 保证使用正确的连接条件，构造将返回不正确的数据。
• 应该总是提供连接条件，否则会产生笛卡尔积。
• 在一个连接中可以包含多个表，甚至对于每个连接可以采用不同的连接类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个连接。这将使故障排除更为简单。

二、内连接查询（WHERE、INNER JOIN）

• 概念：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新纪录。（只有满足条件的记录才能出现在结果关系中）。
• 内连接查询方式：
  • 使用where进行跳过过滤。
  • 使用inner join关联表，然后使用on关键字进行条件过滤。
• 内连接查询也称为等同查询。就是在表关系的笛卡尔积数据记录中，保留表关系中所有相匹配的数据，而舍弃不匹配的数据。

笛卡尔积

• 当在进行select语句连接几个表的时候，相应的关系是在运行时构造的，在数据库的定义中不存在能指示MySQL如何对表进行连接的东西。你必须自己做这些事情。在连接两个表时，你实际上做的是将第一个表中的每一行与第二行表中的每一行配对。
• 笛卡尔积：由没有连接条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
• 叉连接（cross join）：有时候将产生笛卡尔积结果的查询称为“叉连接”。
• 例如，下面是最简单的内连接形式，t1和t2连接起来，那么t1里的每一行将与t2里的每一行进行组合：

SELECT * FROM t1 INNER JOIN t2;

• 在这条语句里，SELECT *的含义是“从FROM子句列出的每个表里选取每个列”。也可以把它写成下面的形式：

SELECT t1.*, t2.* FROM t1 INNER JOIN t2;

• 如果不想选取所有的列，或者想按不同的从左至右的顺序来显示它们，那么可以给出每一个想选的列的名字，并用逗号隔开。
• 避免生成笛卡尔积：
  • 按这种方式来连接表，很可能会产生数量巨大的行，因为最终的行总数是所有表的行数的乘积。假设，有3个表分别包含有100行、200行和300行，那么它们的连接将返回6百万(100x200x300)行。这可是个相当庞大的数字，尽管那3个表本身很小。
  • 在这种情况下，通常需要增加WHERE子句或INNER操作，从而将结果集减少到一个更适合管理的大小，详情见下面操作。

使用WHERE进行条件筛选

• 在上面我们直接使用INNER JOIN将表进行连接，结果会产生很多的行。如果我们想要筛选出一些特定条件的行，那么其中一种方法就是使用WHERE。
• 例如，下面通过WHERE筛选出复合条件的行，因为"INNER JOIN","CROSS JOIN","JOIN"都是等价的，所以下面的语句都是等价的：

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 CROSS JOIN t2 WHERE t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 JOIN t2 WHERE t1.i1 = t2.i2;

• 上面都是通过JOIN把表连接在一起，其中我们还可以只使用逗号把表连接在一起，例如：

SELECT t1.*, t2.* FROM t1, t2 WHERE t1.i1 = t2.i2;

使用WHERE进行内连接查

select vend_name,prod_name,prod_price from vendors,products
where vendors.vend_id=products.vend_id
order by vend_name,prod_name;

使用inner join进行内连接查询

• 我们也可以将where关键字改为“inner join”进行查询，其中inner join使用“on”关键字进行数据筛选
• inner join与where的区别：
  • inner join是ANSI SQL的标准规范，使用inner join连接语法能确保不会忘记连接条件。
  • where子句在某些时候会影响查询的性能。
• 演示案例：使用inner join代替上面的where进行查询，可以产生相同的结果

select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id
order by vend_name,prod_name;

连接多个表进行查询

• select语句中可以连接的表的数目没有限制。创建连接的基本规则也相同。首先列出所有表，然后定义表之间的关系
• 性能考虑：MySQL在运行时关联指定的每个表以处理连接。这种处理可能是非常耗费资源的，因此应该仔细，不要连接不必要的表。连接的表越多，性能下降越厉害
• 演示案例：查询订单号（order_num）为20005的订单的商品的名称（prod_name），商品供应商的名称（vend_name），商品的价格（prod_price）、商品的数量（quantity）

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
where products.vend_id=vendors.vend_id
and orderitems.prod_id=products.prod_id
and order_num=20005;

使用连接查询代替子查询

• 在后一篇文章（https://blog.csdn.net/qq_41453285/article/details/89376355）中我们使用子查询：列出订购物品为TNT2（prod_id）的所有客户的顾客名（cust_name）和顾客联系名（cust_contact）。但是比较复杂，现在我们使用连接查询来完成

select cust_name,cust_contact from customers,orders,orderitems
where customers.cust_id=orders.cust_id
and orderitems.order_num =orders.order_num
and prod_id='TNT2';

三、使用完全限定列名

• 在上面查询我们可以看到，在引用的列可能出现二义性时，必须使用完全限定名（用一个点分割的表名和列名）
• 如果引用一个没有用表名限制的具有二义性的列名，MySQL将会返回错误

演示案例：

• 下面我们对上面的的sql语句修改一处，现在我们想要查看供应商的ID、供应商所有的商品名称以及各个商品的价格
• 如果不使用完全限定：由于两个表中都有vend_id字段，所以查询的时候产生二义性，sql语句不知道vend_id字段来自哪个表（vendors的还是products的？）

select vend_id,prod_name,prod_price from vendors,products
where vendors.vend_id=products.vend_id
order by vendors.vend_id,prod_name;

• 如果使用完全限定：我们在要查询的vend_id字段前加上完全限定，表示vend_id字段来自于vendors表中，因此错误就没有了

select vendors.vend_id,prod_name,prod_price from vendors,products
where vendors.vend_id=products.vend_id
order by vendors.vend_id,prod_name;

五、自连接查询（特殊的内连接）

• 概念：在一个连接查询中，涉及的表都是同一个表，称为自连接查询（是一种特殊的内连接）。
• 特点：在物理上为同一个表，但逻辑上分为两张表。

演示案例

• 要求：如果发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其它物品是否也存在问题，所以查询ID为DTNTR的供应商的其它物品ID和名称。
• 如果不使用自连接，而是用子查询：

SELECT prod_id, prod_name FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

• 如果使用自连接：要使用as声明表别名，防止出现二义性。

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

用自连接而不是子查询

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

六、自然连接（NATURAL JOIN）

• 无论何时对表进行连接，应该至少有一个列出现在不止一个表中（被连接的列）。标准的连接（内部连接）返回所有数据，甚至相同的列多次出现。自然连接排除多次出现，使每个列只返回一次。
• 首先根据表关系中相同名称的字段进行记录匹配，然后去掉重复的字段。
• 还可以理解为在等值连接中把目标列中的重复的属性列去掉，称为自然连接。
• NATURAL JOIN：如果不使用“natural join”，多个表之间用逗号“,”隔开。但是也可以使用“natural join”关键字显式的说明自然连接多个表。

演示案例

• 例如，下面我们将三个表中所有的字段都显示出来（因为表之间有外键约束，所以不同表会有相同的字段名，但是我们使用自然连接相同的字段名就不会重复显示），但是只查询prod_id='FB'的那些行。
• 不使用“NATURAL JOIN”：

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

• 使用“NATURAL JOIN”： 

SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c NATURAL JOIN orders AS o NATURAL JOIN orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

演示案例

• 将水果表和水果供应商表连接在一起（这两个表都有一个相同的字段“f_id”，但是使用自然连接就只显示一个）。

七、外连接查询（LEFT JOIN、RIGHT JOIN）

• 内连接只返回符合查询条件和连接条件的行。外连接查询的结果中不仅包含符合条件的行，还包括左表（左外连接或左连接）或右表（右外连接或右连接）的所有数据行（在相关表中没有关联行的行）。
• 分类：
  • 左外连接或左连接（返回包括左表中的所有记录和左表中连接字段相等的记录）。
  • 右外连接或右连接（返回包括右表中的所有记录和右表中连接字段相等的记录）。

外连接的工作方式

• 以LEFT JOIN为例：
  • 先指定用于对两个表里的行进行匹配的列，接着，当左表的某行与右表的某行相匹配时，两行的内容会被选取为一个输出行。
  • 当左表的某行在右表里无匹配时，它仍然会被选取为一个输出行，只是与它连接的是右表的一个“假”行，其中的每一列都包含NULL。
• 换句话说，对于左表査询出的每一行，不管它在右表里是否有匹配，LEFT JOIN都会强制结果集包含一行记录。在结果集里，对于在右表里没有匹配的结果行，它们会有这样一个特征，即来自右表的所有列都为NULL。
• 这一特征可以让你了解到右表里缺少了哪些行。这是一个既有意思又很重要的特征，因为这种问题会在许多不同的环境里遇到。例如，还没有为哪些顾客指派服务代表？哪些库存商品一件也没卖出？或者，回到我们的sampdb数据库：哪些学生没有参加过某次特定的考试？哪些学生在absence表里没有任何行(即哪些学生属于全勤)？

• 下面是一些使用外连接来完成的工作案例：
  • 对每个客户下了多少订单进行计数，包括哪些至今尚未下订单的客户。
  • 列出所有产品以及订购数量，包括没有人订购的产品。
  • 计算平均销售规模，包括哪些至今尚未下订单的客户。
• 带有OUTER关键字时表示外连接。左连接等价于左外连接，同理，右连接等价于右外连接。因此，OUTER关键字可以省略。
• 使用LEFT JOIN或RIGHT JOIN时，使用ON关键字进行条件过滤（或USING()子句进行筛选，但USING()子句要求被连接的列必须有相同的名字）。
• NATUAL LEFT JOIN类似于LEFT JOIN，它会按照LEFT JOIN规则对左右两个表里所有的同名的列进行匹配（因此，它不需要指定任何ON或USING子句）。RIGHT JOIN同理。

左外连接演示案例

• 以t1表和t2表为例：

SELECT * FROM t1;
SELECT * FROM t2;

• 内连接查询将只返回两个表相关联的行：

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;

• 如果使用左外连接，将返回t1表所有的行，对于t2表只返回条件相关的行，对于t1表在t2表中没有的行，t2将显式为NULL。这样就可以知道t1表中有哪些内容在t2表中不存在了。

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2;

左外连接演示案例

• 需求：想要顾客的ID（cust_id）与顾客订单的ID（order_num）。
• 如果不使用左外连接，而是用内连接：那么内连接不会把没有订单的顾客显示出来，只显示有订单信息的行。

• 如果我们使用左外连接：那么会显示左表（customers）中所有的顾客ID，那么再去匹配右表（orders）中对应的订单编号（备注：因为10002顾客没有订单，所以其订单号为NULL）。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

左外连接演示案例

• 在customers和orders表中，查询所有客户，包括没有订单的客户，SQL语句如下（因为左外连接会显示左表所有的数据，而c_id为10002的用户没有o_num，所以显示为NULL）：

右外连接演示案例

• 与上面的左外连接类似，但是我们这次想查询右表（orders）中所有的订单ID，并匹配对应的左表（customers）中的顾客ID。

select customers.cust_id,orders.order_num
from customers right outer join orders
on customers.cust_id=orders.cust_id;

右外连接演示案例

• 在customers和orders表中，查询所有的订单信息，包括没有订单的客户，SQL语句如下（因为右外连接会显示右表所有的数据，此个orders表中o_num等于30004的订单我们没有设置相应的c_id，所以显示为NULL）：

NULL注意事项

• 在外连接时，如果表2在表1中没有相关行，那么表2该行将显式为NULL。
• 注意：这种情况只有当表2的行字段类型为"NOT NULL"类型时才不会有歧义。如果表2行字段类型允许为NULL，那么你将无法区分NULL是因为未拥有，还是因为该行已经匹配但是值为NULL。

利用外连接筛选出我们想要的行

• 例如，我们可以使用外连接查询出"在左表中拥有，而在右表中没有的行"，则可以使用下面的SQL语句：

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL;

• 上面已经查出了该行，但是只有t1表中有数据，t2表中无数据，因此我们可以只提出t1中的数据，则可以写出下面的SQL语句：

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL;

一个完整的演示案例

• 之前提到过，LEFTJOIN非常适合用于解决像“缺失了哪些值”这样的问题。接下来，我们将把这个原则应用到sampdb数据库上，并一起来看一个比早先使用t1和t2的示例更为复杂的示例。
• 前面文章我们使用到了一个成绩考评项目：
  • 有一个记录学生的student表。
  • 有一个记录已发生考试或测验事件的grade_event表。
  • 还有一个记录每位学生的每次考试或测验成绩的score表。
• 不过，如果某个学生在某次考试或测验的当天生病了，那么score表里就不会有该学生该次事件的成绩。因故误考的学生需要参加补考，可是要怎样才能把这些缺失的行找出来呢？
• 要解决的问题是：确定哪些学生在给定考试事件里没有成绩。为此，需要针对每一个考试事件执行一遍。也就是说，我们需要找出哪些学生和事件的组合没在score表里出现过。这种“哪些值未出现过”的说法即是需要执行LEFTJOIN操作的一种提示。这种连接操作可不像前面那几个示例那么简单。我们不只是要查找那些在某个列里不存在的值，而且要査找不存在于两个组合列的那些值。我们想要的组合是所有的“学生+事件”。可以通过连接student表和grade_event表，来产生这些组合：

FROM student INNER JOIN grade_event

• 接下来，我们需要利用这个连接结果，与score表一起执行一个LEFT JOIN操作，以便找到“学生ID/考试ID”对的所有匹配：

FROM student INNER JOIN grade_event
  LEFT JOIN score ON student.student_id = score.student_id
    AND grade_event.event_id = score.event_id

• 请注意，ON子句会导致score表里的行按照该连接操作前面列出的不同表里的匹配情况进行连接。这是解决此问题的关键。这个left join操作会针对student表和grade_event表通过连接而得到的每一个行，强制生成一个行，即使在score表里没有对应的行也没关系。缺少分数行的结果行可以根据这样一个事实标识出来，即来自score表的列将全部为NULL。我们可以通过在WHERE子句里增加一个条件，把这些行找出来。来自score表的所有列都可以这样做，但因为我们正在査找缺失的考试分数行，所以对score列进行测试是顺理成章的事：

WHERE score.score IS NULL

• 我们还可以用一个ORDER BY子句对结果进行排序。最符合逻辑的两种顺序是：按先学生后考试的顺序和按先考试后学生的顺序。这里选择的是第一种：

ORDER BY student.student_id, grade_event.event_id

• 现在，所有需要做的事情就是列出想要在输出里看到的那些列的名字。下面是最终的语句：

SELECT student.name, student.student_id, grade_event.date, grade_event.event_id, grade_event.category
 FROM student INNER JOIN grade_event
   LEFT JOIN score ON student.student_id = score.student_id
     AND grade_event.event_id = score.event_id
WHERE score.score IS NULL
ORDER BY student.student_id, grade_event.event_id;

• 这里有个小细节需要解释一下。输出结果里显式了学生ID和事件ID。由于student_id列在student表和score表里都有，因此你或许会认为在输出结果里也应该列出名字student.student_id或score.student_id。但事实并非如此，之所以能找到我们感兴趣的行，那是因为对于score表里的全部列，LEFT JOIN返回的都是NULL值。如果选择score.student_id，那么在输出结果里只会产生一个全部值都为NULL的列。在决定要显示哪个表的event_id列时，也需要考虑相同的原则。它在grade_event表和score表里都有出现，但因为score.event_id值总是为NULL，所以这条査询命令选择了grade_event.event_id列。

八、聚合函数在连接中的使用

• 聚合函数用来汇总数据。至今为止我们的聚集函数都只是在单个表中汇总数据，但这些函数也可以与连接一起使用。

内连接演示

• 检索所有客户及每个客户所下的订单数（最后必须使用group by进行分组）。

select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord
from customers inner join orders
on customers.cust_id=orders.cust_id
group by customers.cust_id;

左外连接演示

• 与上面的内连接相似，此处我们使用左外连接，查询所有顾客的订单数量（包括没有订单的客户）。

select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord
from customers left outer join orders
on customers.cust_id=orders.cust_id
group by customers.cust_id;

九、符合条件连接查询

• 概念：在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更加准确。

演示案例

• 在customers和orders表中，使用inner join语法查询customers表中的ID为10001的客户的订单信息。

演示案例

• 在fruits和suppliers表之间，使用inner join进行内连接查询，并对查询结果进行排序