多表查询

卤蛋七号
460 阅读9分钟

简介

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

笛卡尔积

    笛卡尔乘积是一个数学运算，假设我有两个集合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加入有效的连接条件。
  • 加入连接条件后，查询语法：
SELECT t1.column,t2.column FROM t1,t2 WHERE t1.column1 = t2.column2;
  • 正确写法：
#查询所有员工对应的其部门名称
SELECT name,dept_name FROM user,dept WHERE user.dept_id = dept.id;
  • 在表中有相同列时，在列名之前加上表名前缀。

多表查询分类讲解

分类1: 等值连接vs非等值连接

等值连接

SELECT name,dept_name FROM user,dept WHERE user.dept_id = dept.id;
  • 多个连接条件用AND操作符
#查询所有员工对应的其部门名称，并且员工的年龄大于25周岁。
SELECT name,dept_name FROM user,dept WHERE user.dept_id = dept.id AND user.age > 25;
  • 区分重复的列名
    • 多个表中有相同的列，必须在列名之前加上表名前缀。
    • 在不同的表中具有相同列名的列可以用表名加以区分。
  • 表的别名
    • 使用别名可以简化查询。
    • 列名前使用表名前缀可以提高查询效率。
SELECT u.name,d.dept_name FROM user AS u,dept AS d WHERE u.dept_id = d.id AND u.age > 25;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原来的表名，否则就会报错。
[强制]对于数据库中表记录的查询和变更，只要设计多个表，都需要在列名前加表的别名(或表名)进行限定。
说明:对表进行查询记录,更新记录,删除记录，如果对操作列没有限定表的别名(或表名)，并且操作列在多个表中存在时，就会抛异常

  • 连接多个表

总结:连接n个表，至少需要n-1个连接条件。

非等值连接

#查询所有员工薪资级别
SELECT u.name,u.salary,sg.level FROM user AS u,salary_grades AS sg WHERE u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;
SELECT u.name,u.salary,sg.level FROM user AS u INNER JOIN salary_grades AS sg ON u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;

分类2: 自连接vs非自连接

table1和table2本质上是同一张表，只是用别名的方式虚拟成两张表以代表不同的意义。然后两张表进行内连接，外连接等查询。

#查询员工所对应的经理(内连接)
SELECT CONCAT(u.name,' works fro ',us.name) FROM user AS u,user AS us WHERE u.manager_id =us.id;
  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行；
  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这种连接被称为左(或右)外连接，没有匹配的行时,结果表中响应的列为空(null)；
  • 如果是左外连接,那么连接条件中左边的表也称为主表,右边的表称为从表；
  • 如果是右外连接，那么连接条件中右边的表也称为主表，左边的表称为从表；
#内连接
SELECT u.name,u.salary,sg.level FROM user AS u,salary_grades AS sg WHERE u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;
SELECT u.name,u.salary,sg.level FROM user AS u INNER JOIN salary_grades AS sg ON u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;
#外连接(左)
SELECT u.name,u.salary,sg.level FROM user AS u LEFT JOIN salary_grades AS sg ON u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;
#外连接(右)
SELECT u.name,u.salary,sg.level FROM user AS u RIGHT JOIN salary_grades AS sg ON u.salary BETWEEN sg.lowest_sal AND sg.highest_sal;

SQL语法实现多表查询

  • 使用JOIN...ON子句创建连接的语法结构
SELECT table1.column,table2.column,table3.column FROM table1 JOIN table2 ON table1 和 table2 的连接条件 JOIN table3 ON table2 和 table3 的连接条件;

SQL99采用的这种嵌套结构非常清爽，层次性更强，可读性更强，即使再多的表进行连接也都清晰可见。如果采用SQL92，可读性就会大大折扣。

  • 语法说明:
    • 可以使用ON子句指定额外的连接条件；
    • 这个连接条件是与其它条件分开的；
    • ON子句使语句具有更高的易读性；
    • 关键字JOIN,INNER JOIN,CROSS JOIN的含义是一样的,都表示内连接；

内连接(INNER JOIN)的实现

语法：
SELECT 字段列表 FROM A表 INNER JOIN B表 ON WHERE 等其他子句;

外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

语法：

  • SELECT 字段列表 FROM A表 LEFT JOIN B表 ON WHERE 等其他子句；
  • SELECT 字段列表 FROM A表 LEFT OUTER JOIN B表 ON WHERE 等其他子句；

右外连接(RIGHT OUTER JOIN)

  • SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON WHERE 等其他子句；
  • SELECT 字段列表 FROM A表 RIGHT OUTER JOIN B表 ON WHERE 等其他子句；

需要注意的是, LEFT JOIN和RIGHT JOIN只存在于SQL99及以后的标准中，在SQL92中不存在，只能用(+)表示。

满外连接(FULL OUTER JOIN)

  • 满外连接的结果=左右表匹配的数据+左表没有匹配的数据+右表没有匹配到的数据；
  • SQL99是支持满外连接的,使用FULL JOIN 或 FULL OUTER JOIN 来实现的；
  • 需要注意的是，mysql不支持FULL JOIN，但是可以使用LEFT JOIN UNION RIGHT JOIN 代替；

UNION的使用

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

UNION操作符

UNION操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符

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

#查询用户信息表，名字包含“德”字或者年龄大于30的数据
#方式1
SELECT * FROM user WHERE name LIKE '%德%' OR age >30;
#方式2
SELECT * FROM user WHERE name LIKE '%德%'
UNION
SELECT * FROM user WHERE age >30;

7中SQL JOINS的实现

内连接：

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的等值连接，他与下面的SQL查询结果是相同的：

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

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

  • WHERE：适用于所有的关联查询；
  • ON：只能跟JOIN一起使用，只能写关联条件，虽然关联条件可以并到WHERE中和其他条件一起写，但是分开写可读性更好；
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值等；
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);

注意:我们要控制连接表的数量,多表就相当于嵌套for循环一样,非常消耗资源,会让SQL查询性能下降的很严重,因此不要连接不必要的表,在许多DBMS中,也都会有最大连接表的限制.
[强制]超过三张表禁止join,需要join的字段,数据库类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引

avatar
文章
阅读
粉丝