连接查询-多表联合查

关系型数据库通常将不同的实体对象和它们之间的联系存储在多个表 中，例如电商系统中使用的产品表、用户表、订单表以及订单明细表等。当 我们查看某个订单信息时，需要同时从这几个表中查找关于该订单的相关数据。在 SQL 语句中，我们可以使用连接（JOIN）查询来获取多个表中的关联数据。

一、连接查询的分类

根据表的连接方式，连接查询分为交叉连接、内连接、外连接、自连接和自然连接。

• 交叉连接
• 内连接：
  • 等值连接
  • 非等值连接
• 外连接：
  • 左外连接（左连接）
  • 右外连接（右连接）
  • 全外连接（全连接）
• 自然连接

二、笛卡尔积（交叉连接）

交叉连接也被称为笛卡儿积（Cartesian Product），使用关键字 CROSS JOIN 表示。两个表的交叉连接将一个表的所有数据行和另一个表的所有数据行进行两两组合，返回结果的数量为两个表中的行数相乘。例如， 一个 100 行数据的表和一个 200 行数据的表进行交叉连接查询将会产生 20 000 行数据。交叉连接的原理如图 6.5 所示。

交叉连接使用的场景比较少，一般用于生成大量测试数据。

注意：如果表中的数据量比较大，交叉连接可能会导致查询结果的数据 量急剧膨胀，从而引起性能问题。我们通常应该指定连接条件，避免产生交叉连接。

三、内连接

1.等值连接

连接查询中的 ON 子句与 WHERE 子句类似，可以支持各种条件运算 符。其中最常用的是等号（=）运算符，这种连接查询也被称为等值连接， 例如：

等值连接返回两个表中连接字段值相等的数据，我们最常使用的连接就 是等值连接。

2.非等值连接

除等号运算符外，连接条件中也可以使用其他比较运算符或者逻辑运算 符，比如 >=、！=、BETWEEN、AND 等，这种连接查询被称为非等值连 接，如下所示：

我们将职位编号不相等以及员工的月薪位于「开发经理」月薪的范围之 内作为连接条件，返回当前月薪属于开发经理级别但不是开发经理的员工。

四、外连接

1.左外连接（左连接）

【数据表A中的记录为主循环体，依次匹配数据表B中的记录，如果数据表A中连接字段Aid的值，在数据表B中没有Bnameid值与之对应，则右侧以null代替】

2.右外连接（右连接）

 数据表B中的记录为主循环体，依次匹配数据表A中的记录，如果数据表B中连接字段Bnameid的值，在数据表A中没有Aid值与之对应，则左侧以null代替

3.全外连接（全连接）

五、自连接

有些情况下，需要联接自己才能完成相关的查询操作，此时的联接被称为自连接，此时联接的两张表为，自己和自己，为了避免混淆，需要使用别名进行区分。比如：

select *
from products as p1,products as p2
where p1.ID = p2.ID

例如，员工表中的经理字段（manager）引用了员工表自身的编号字段 （emp_id）。如果我们想要查看员工以及他的经理，可以通过自连接查询实现：

由于自连接中同一个表（employee）出现了两次，我们必须使用表别 名进行区分。其中别名 e 代表了员工，别名 m 代表了经理。查询返回的结 果如下：

          

另外，我们在查询中使用了左外连接，因为员工表中存在没有上级经理 的员工（刘备）。

六、自然连接

如果连接查询同时满足以下条件，我们可以使用USING 替代ON 来简化连接条件的输入：

    ● 连接条件是等值连接。

    ● 两个表中的连接字段名称相同，类型也相同。

例如，将上述的内连接-等值连接查询可以使用 USING 关键字简化如下：

其中，USING 表示使用两个表中的公共字段（job_id）进行等值连接。 另外，查询语句中出现的公共字段无须添加表名限定。

除 Microsoft SQL Server 外，其他 4 种数据库都支持 USING 关键字。 进一步来说，如果等值连接条件中包含了两个表中所有同名同类型的字 段，查询语句可以继续进行简化。例如，员工表和职位表中只存在 1 个同名 同类型的字段（job_id），因此上面的示例可以进一步修改为下面这样：

其中，NATURAL JOIN 表示自然连接，我们同时省略了连接条件，表 示使用两个表中的所有同名同类型字段进行等值连接。 同样，除 Microsoft SQL Server 外的其他 4 种数据库都支持自然连接。

参考:

左连接，右连接，内连接及全连接区别_内连接,左连接,右连接作用及区别-CSDN博客