多表查询
案列说明
笛卡尔积的理解
select id,department_name
from employees,departments;#错的
select id,department_id,department_name
from employees CROSS JOIN departments;#错的
每个员工和每个部门匹配了一遍(查出的条目数=id数*department数)
错误原因:缺少连接条件
笛卡尔积的解决
编写连接条件: 表1.列 = 表二.列(若多个表连接, _至少_要用n-1个连接条件)
select id,employees.name,department_name
from employees,departments
WHERE employees.name = departments.name;
注:如果要显示的列在要查询的表中名字一样,则要表明,是出自哪个表, eg: employees.name
建议在多表查询时,标明显示的是哪个表的信息 (优化)
优化:可以在FROM后使用表的别名, 但是一旦使用别名,后续就一定要都用别名
多表查询的分类
等值连接和非等值连接
- 等值连接:上述的带有=的
- 非等值连接:没有=的
select t1.id,t1.name,t2.grade
from employees t1,departments t2
WHERE ti.salary BETWEEN t2.lowest_salary AND t2.highest_salary ;#非等值
自连接和非自连接
- 非自链接:表1和表2连接
- 自链接:表1和自己连接
#显示员工(t1)和其管理者(t2)的基本信息
select t1.id,t1.name,t2.id,t2.name
from employees t1,employees t2#一个表看作两个表
WHERE t1.manage_id = t2.id ;#自连接
内连接和外连接
- 内部联接:合并具有相同列的表,不包括表之间不匹配的行
[En]
Internal join: merge tables with the same column, excluding rows that do not match one table with another*
- 外部联接:除了内联接的结果外,还合并具有相同列和查询不匹配行的表[En] external join: merge tables with the same column and query * mismatched rows * in addition to the results of the inner join*
外部连接的分类:左侧外部连接(左侧表,右侧)、右侧外部连接(右侧表,左侧)、完全外部连接
[En]
Classification of outer joins: left outer joins (left table, right side), right outer joins (right table, left side), full outer joins
SQL92:使用(+)创建连接
内连接:见上
外连接:左表有数据不匹配,在右表加(+);反之,在左表加(+), 但是MySQL不支持
WHERE t1.department_id = t2.department_id(+)#左连接
SQL99:使用JOIN…ON的方式
内连接
select t1.id,t1.name,t2.department_name,t3.environment
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id
JOIN locations t3#加入第二个人表
ON t2.department_location = t3.department_location;
外连接
使用OUTER JOIN…ON…
- 左外连接:LEFT OUTER JOIN
- 右外连接:RIGHT OUTER JOIN
- 满外连接:FULL OUTER JOIN(MySQL不支持)
select t1.name,t2.department_name#左外连接
from employees t1 LEFT OUTER(可省略) JOIN departments t2
ON t1.department_id = t2.department_id;
UMION的使用
合并查询结果
SELECT colum... FROM table1
UNION (ALL)
SELECT colum... FROM table2
- UNION操作符
两个查询结果的并集, 去重(效率低)
- UNION ALL操作符(推荐)
两个查询结果的并集, 不去重(效率高)
7种SQL JOINS的实现
中图(内连接):
select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id;
左上图(左外连接):
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id;
右上图(右外连接):
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id;
左中图:
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL;
右中图:
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
左下图(满外连接):
#方式一:左上图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
#方式二:左中图 UNION ALL 右上图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id;
右下图:
#左中图 UNION ALL 右中图
select t1.name,t2.department_name
from employees t1 LEFT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t2.department_id IS NULL
UNION ALL
select t1.name,t2.department_name
from employees t1 RIGHT JOIN departments t2
ON t1.department_id = t2.department_id
WHERE t1.department_id IS NULL;
SQL语法新特性
自然连接
使用关键字:NATURAL JOIN(不灵活),自动查询表中所有相同字段,然后进行等值连接
USING连接(不适用于自连接)
使用关键字:USING(同名字段),将表中相同名字的字段自动等值连接
select t1.name,t2.department_name
from employees t1 JOIN departments t2
ON t1.department_id = t2.department_id;
等价于
select t1.name,t2.department_name
from employees t1 JOIN departments t2
USING(department_id);
Original: https://www.cnblogs.com/wht-de-bk/p/15966054.html
Author: T,a,o
Title: MySQL多表查询
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/507786/
转载文章受原作者版权保护。转载请注明原作者出处!