连接查询，又称为多表查询，当查询的字段来自多个表，就会用到多表查询。实际数据工程项目开发中用到最多的就是连接查询。复杂的数据查询逻辑更多的是多表连接和子查询的复合查询类型。掌握是多表查询是一个DBA或后端/全栈开发者最起码的基本功。

在连接查询中，SELECT 子句和WHERE 子句的条件筛选 用到的所有字段 需要显式指定<表名.字段名>，以告知MySQL读取的字段来自哪一个表。

进行本文前，请完成如下数据导入

 USE testdb;

-- testdb.customer definition

CREATE TABLE `customer` (
  `cid` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- testdb.order_details definition

CREATE TABLE `order_details` (
  `oid` int NOT NULL,
  `number` varchar(20) NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `customerId` int DEFAULT NULL,
  `orderTypeId` int DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `order_details_FK` (`customerId`),
  KEY `order_details_FK_1` (`orderTypeId`),
  CONSTRAINT `order_details_FK` FOREIGN KEY (`customerId`) REFERENCES \
     `customer` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `order_details_FK_1` FOREIGN KEY (`orderTypeId`) REFERENCES \
     `order_type` (`oid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- testdb.order_type definition

CREATE TABLE `order_type` (
  `oid` int NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO customer(cid,name,age)
VALUES(1,'Mary',32);
INSERT INTO customer(cid,name,age)
VALUES(2,'Kitty',44);
INSERT INTO customer(cid,name,age)
VALUES(3,'Bob',33);
INSERT INTO customer(cid,name,age)
VALUES(4,'Tim',28);
INSERT INTO customer(cid,name,age)
VALUES(5,'Nancy',NULL);

INSERT INTO order_type(oid,name)
VALUES(1,'Local');
INSERT INTO order_type(oid,name)
VALUES(2,'OutsideState');
INSERT INTO order_type(oid,name)
VALUES(3,'OverSea');
INSERT INTO order_type(oid,name)
VALUES(4,'Cancel');


INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(1,U001',1500,2,2);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(2,'S001',3200,1,3);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(3,'L003',734,3,1);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(4,'T001',1005,4,2);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(5,'S003',230,3,3);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(6,'C001',843,4,4);
INSERT INTO order_details(oid,number,price,customerId, orderTypeId)
VALUES(7,'L002',220,NULL,1);

E-R关系图

通过查询各个表的初始数据，如下图所示

顾客表

订单类型

订单明细

那么没问题的话，我们可以正式进入正题了

连接查询的分类

SQL99标准之后，几乎所有主流的数据库都支持连接查询，表连接查询分为

其语法

SELECT <查询列表> FROM <表1> <连接类型> JOIN <表2> ON <连接条件> WHERE <筛选条件>

内连接查询

内连接在多表查询中使用得最为频繁，内连接查询将两张表通过连接条件连接起来。例如我们查询所有客户的名称(位于customer表中),订单类型、价格等信息，后面两个字段位于order_details表中。我知道，customer表和order_details表是通过customer.cid字段和order_details.customerId字段可以连接两个表。

那么我们可以将两个内连接查询，完成上面的查询需要。可以怎么做

SELECT 
	o.`number` '订单号',
	c.name '顾客名称',
	c.age '年龄',
	o.price '价格'
FROM order_details AS o 
INNER JOIN customer AS c 
ON o.customerId=c.cid

上面的语句列出了查询结果所需的字段名,

• 通过 FROM 关键字后指定需要的表,在INNER JOIN 之前的表,我们叫主表
• INNER JOIN 关键字指定被连接的表（我们叫从表）
• ON 关键字后则指定了两个表的连接条件。

输出如下,内连接查询实质上是取两个表相关字段的交集信息。值得注意的是，不论什么类型的连接查询类型，显式使用表的表明是一个非常好的习惯。

当然我们可以进一步通过WHERE子句添加筛选条件，例如查询超过30岁的客户订单明细

SELECT 
	o.`number` '订单号',
	c.name '顾客名称',
	c.age '年龄',
	o.price '价格'
FROM order_details AS o 
INNER JOIN customer AS c 
ON o.customerId=c.cid
WHERE c.age>30;

输出如下

外连接查询

内连接返回的结果集就要求两个表必须满足连接条件的匹配记录，如果你对比单个order_details表的查询记录L002的记录显示出来，因为在order_details表中L002对应的customerId是NULL，由于内连接的连接条件order_details.customerId=customer.cid是无法匹配NULL的条目，因此L002的记录在内连接查询中是不会显示的。有时在多表连接查询中，我们需要显示条目中为NULL的记录。那么就要用到外连接查询。

外连接查询除了保留了内连接查询的行为(就是取两个表的交集)之外，就是对NULL的处理，外连接也会把具有NULL值的记录添加到结果集合当中。

左外连接查询

先看一个实例

SELECT
	A1.number '订单号',
	A1.price '价格',
	A1.orderTypeId '订单类型',
	A2.name '顾客姓名',
	A2.age '顾客年龄'
FROM order_details AS A1
LEFT JOIN customer AS A2
ON A1.customerId=A2.cid;

输出如下图所示，会显示L002对应顾客名称为NULL的记录。因此不论在右表中是否存在匹配的记录,左外连接查询会将所有记录都放到结果集中：

右外连接查询

SELECT
	A1.number '订单号',
	A1.price '价格',
	A1.orderTypeId '订单类型',
	A2.name '顾客姓名',
	A2.age '顾客年龄'
FROM order_details AS A1
RIGHT JOIN customer AS A2
ON A1.customerId=A2.cid;

输出如下图，与左外连接正好相反，右外连接不论是否有匹配的结果，都会返回右表中的所有记录。

全外连接

需要注意的是MySQL到目前为止并不支持全外连接，对全外连接支持完善的是Oracle和MS SQL Server平台。

全外连接查询其实就是左外连接和右外连接查询的并集。我们可以通过UNION运算符将左外连接的查询语句和右外连接查询连接两个结果集取并集，得到相同的结果。

SELECT
	A1.number '订单号',
	A1.price '价格',
	A1.orderTypeId '订单类型',
	A2.name '顾客姓名',
	A2.age '顾客年龄'
FROM order_details AS A1
LEFT OUTER JOIN customer AS A2
ON A1.customerId=A2.cid
UNION
SELECT
	A1.number '订单号',
	A1.price '价格',
	A1.orderTypeId '订单类型',
	A2.name '顾客姓名',
	A2.age '顾客年龄'
FROM order_details AS A1
RIGHT OUTER JOIN customer AS A2
ON A1.customerId=A2.cid;

输出结果，如下图

交叉连接查询

与内连接相比，交叉连接可以省略ON子句，它会将涉及所有表的所有记录都返回到结果集当中。

有两种方式可以实现交叉连接查询，一种是隐式的交叉连接，即只需在FROM子句后面将需要交叉连接的表明罗列即可。下面如下示例所示

SELECT 
	A2.cid '顾客编号',
	A2.name '顾客姓名',
	A2.age '顾客年龄',
	A1.oid '订单ID',
	A1.number '订单号',
	A1.price '价格'
FROM customer AS A2,order_details AS A1;

另外一种是在两个连接表明之间显式定义CROSS JOIN

SELECT 
	A2.cid '顾客编号',
	A2.name '顾客姓名',
	A2.age '顾客年龄',
	A1.oid '订单ID',
	A1.number '订单号',
	A1.price '价格'
FROM customer AS A2 
CROSS JOIN order_details AS A1;

输出结果，从输出结果看来，交叉连接适合做那种两个表的相关记录做多对多搭配的应用场景。然而这里显示是没卵用的。

自连接查询

自连接查询其实是内连接查询的一个特例，内连接查询的连接的是两张不同的表，而自连接它们连接的是同一张表，也就是同一张表，自连接查询会查询两次。

SELECT 
	A1.number '订单号',
	A1.price '价格',
	A1.orderTypeId '订单类型', 
	A2.number  '订单',
	A2.price   '价格',
	A2.orderTypeId '订单类型' 
FROM order_details AS A1
JOIN order_details AS A2
ON A1.orderTypeId=A2.orderTypeId;

输出如下图

虽然这个输出从SQL语句执行上是没有任何问题的，但人为实用角度来说，它是没有任何实际意义的。因为它显示都是一些冗余的数据条目。

示例2：自连接非常适合一种应用场景，这也是前篇基于SQL92语法谈到的。自连接查询就非常适合这种多个字段具有从属关系，并且这些字段同源于同一张表。我们希望显示每个员工的同时显示对应的领导名称。

USE myemployees;

SELECT 
	E1.emp_id '名字',
	CONCAT(E1.first_name,' ',E1.last_name) '职员姓名',
	E2.leader_id '领导工号',
	CONCAT(E2.first_name,' ',E2.last_name) '上司姓名'
FROM employees AS E1
JOIN employees AS E2
ON E1.leader_id=E2.emp_id;

输出如下图

非等值连接查询

上文所说的都是等值连接查询，换句话说，

• 等值连接查询就是其连接条件使用“=”操作符号连接主表和从表关联字段的连接查询。
• 非等值查询就是其连接条件并非“=”操作付的连接查询。

这里介绍一种SQL92语法规范的，非等值连接查询的适用场景比较少，平时数据库管理、项目开发中用到也比较少。这里仅介绍一种应用场景，例如在工资表中除了显示每个员工的工资明细，我们还要显示该工资所属的待遇水平区间。

我们有两个表分别是salary表、salary_level表。比如下图左边的salary表，我们希望右边增加一列工资等级的明细，例如24000这个工资属于B级、9000属于D级

具体的示例，如下查询语句所示

SELECT 
	E.first_name '名字',
	E.salary '工资',
	J.grade_level '工资级别'
FROM salary_level AS J,employees AS E
WHERE E.salary BETWEEN J.minSalary AND J.maxSalary
ORDER BY 工资 DESC;

上面代码的意思是，Mysql会将salary表每行的salary字段中的数据跟salary_level表的每一行做minSalary字段和maxSalary字段进行对比，上面的WHERE子句中的筛选条件正好刚这事。

如图所示

小结：

最后，我们用一个图来结束本篇，我们可以将不同类型的连接查询用集合的思维方式进行归纳总结。

还有两种种特殊形态的全外类型

下一篇有待更新....