关联查询

卤蛋七号
335 阅读5分钟

简介

多表查询，也称为关联查询，指两个或者更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系(一对一，一对多，多对多)，他们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键，比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

笛卡尔积

笛卡尔乘积是一个数学运算，假设我有两个集合X，Y那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能，组合的个数即为两个集合中元素个数的乘积数。

SQL92中，笛卡尔积也被称作交叉连接，英文名是CROSS JOIN。在SQL99中也是使用CROSS JOIN表示交叉连接，它的作用就是可以把任意表进行连接，即使两张表不相关。在MySQL中如下情况会出现笛卡尔积：

笛卡尔积问题：

  • SELECT name,dept_name FROM user,dept;
  • SELECT name,dept_name FROM user CROSS JOIN dept;
  • SELECT name,dept_name FROM user INNER JOIN dept;
  • SELECT name,dept_name FROM user JOIN dept;

笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件(或关联条件)
  • 连接条件无效(或关联条件无效)
  • 所有表中的所有行互相连接

解决笛卡尔积方案：

  • 为了避免笛卡尔，可以在WHERE加入有效的连接条件。

多表查询分类讲解

等值连接 & 非等值连接

  • 等值连接：最常见的连接操作，通常是存在 主外键约束 条件的多表建立的，连接条件中两个字段通过 = 建立等值关系(连接条件是等号)。
  • 非等值连接：多表连接，连接条件为 不是用等号，用大于、小于、大于等于、小于等于。

内连接 & 外连接

  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。
  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这种连接被称为左(或右)外连接，没有匹配的行时,结果表中响应的列为空(null)；
    • 如果是左外连接，那么连接条件中左边的表也称为主表，右边的表称为从表。
    • 如果是右外连接，那么连接条件中右边的表也称为主表，左边的表称为从表。

UNION的使用

合并查询结果，利用UNION关键字，可以给出多条SELECT语句，并将他们的结果集组合成单个结果集。合并时，两个表对应列数和数据类型必须相同，并且相互对应，各个SELECT语句之间使用UNION 或 UNION ALL关键字进行分割。

语法格式:SELECT COLUMN,... FROM TABLE1 UNION [ALL] SELECT COLUMN,... FRON TABLE2;

  • UNION操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL 操作符返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句所需要的资源比UNION语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据的查询效率。

7种SQL JOIN的实现

内连接：

SELECT * FROM A JOIN B ON 关联条件;
SELECT * FROM A INNER JOIN B ON 关联条件;

左外连接：

SELECT * FROM A LEFT JOIN B ON 关联条件;

右外连接：

SELECT * FROM A RIGHT JOIN B ON 关联条件;

A-A∩B(显示A表中特有的数据)：

SELECT * FROM A LEFT JOIN B ON A.KEY=B.KEY WHERE B.KEY IS NULL;

B-A∩B(显示B表中特有的数据)：

SELECT * FROM A RIGHT JOIN B ON A.KEY=B.KEY WHERE A.KEY IS NULL;

A∪B(满外连接)：

SELECT * FROM A LEFT JOIN B ON A.KEY=B.KEY WHERE B.KEY IS NULL
UNION ALL
SELECT * FROM A RIGHT JOIN B ON A.KEY=B.KEY;

SELECT * FROM A LEFT JOIN B ON 关联条件
UNION
SELECT * FROM A RIGHT JOIN B ON 关联条件;

A∪B-A∩B或(A-A∩B)∪(B-A∩B)：

SELECT * FROM A LEFT JOIN B ON A.KEY=B.KEY WHERE B.KEY IS NULL
UNION ALL
SELECT * FROM A RIGHT JOIN B ON A.KEY=B.KEY WHERE A.KEY IS NULL;

SQL99语法新特性

自然连接

SQL99在SQL92的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。他会帮助你自动查询两张连接表中所有的相同字段，然后进行等值连接。

在SQL92标准中：

SELECT employee_id,last_name,department_name FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在SQL99标准中：

SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;

USING连接

当我们进行连接的时候，SQL99还支持使用USING指定数据表里的同名字段进行等值连接。但是只能匹配和JOIN一起使用

SELECT employee_id,last_name,department_name FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接NATURAL JOIN不同是，USING指定了具体的相同的字段名称，你需要在USING的括号()中填入要指定的的同名字段，同时使用JOIN....USING可以简化JOIN ON的等值连接。

表连接的约束条件可以有三种方式：WHERE、ON、USING

  • WHERE：适用于所有的关联查询。
  • ON：只能跟JOIN一起使用，只能写关联条件，虽然关联条件可以并到WHERE中和其他条件一起写，但是分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值等。

多表查询注意点

我们要控制连接表的数量，多表就相当于嵌套for循环一样，非常消耗资源，会让SQL查询性能下降的很严重，因此不要连接不必要的表，在许多DBMS中，也都会有最大连接表的限制。

[强制]超过三张表禁止join，需要join的字段，数据库类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

avatar
文章
阅读
粉丝