1、多表查询
多表查询也称为关联查询,指的是两个或更多个表一起完成查询操作。
前提条件:
这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段的,这个关联字段可能是建立了外键,也有可能是没有建立外键。
1.1-笛卡尔积(交叉连接)
说到多表连接必须要说一下笛卡尔积!笛卡尔积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。
如图: 笛卡尔积其实是一种错误的多表连接,是因为在多表连接的时候没有连接条件或者连接条件错误导致的。下面就仔细分析一下情况。
1.1.1-笛卡尔积(交叉连接)产生条件
1、省略多个表的连接条件
2、连接条件(关联条件)无效
3、所有表中的所有行互相连接
1.1.2-笛卡尔积(交叉连接)如何避免
1、可以在where加入有效的连接条件
2、在表有相同列时,在列名之前加上表名前缀
1.2-多表连接查询分类
1.2.1分类1:等值连接 VS 非等值连接
等值连接
举例: 拓展1:多个连接条件和AND操作符 拓展2:区分重复的列名 多个表有相同列时,必须在列名之前加上表名前缀 在不同表中具有相同列名的列可以用表名加以区分 拓展3:表的别名 使用别名可以简化查询 列名前使用表名前缀可以提高查询效率 拓展4:连接多个表 连接n个表,至少需要n-1个连接条件。 举例: ![在这里插入图片描述](https://img-blog.csdnimg.cn/6464c3b81eff4ea7831d877db6fb2ce6.png)
非等值连接
举例: 查询某列的值在什么范围之间就是非等值连接 ![在这里插入图片描述](https://img-blog.csdnimg.cn/21fc6829cdb74db487eec806446135a3.png)
1.2.2分类2:自连接 VS 非自连接
自连接:自连接就是两张表其实是同一张表的数据,然后利用取别名的方式虚拟成两张表以代表不同的意义,然后两张表再进行内连接和外连接等查询。
非自连接:就是两种数据不同的表进行连接查询。
1.3-SQL99语法实现多表查询
格式:
使用JOIN...ON子句创建连接的语法结构
SELECT table1.column, table2.column,table3.columnFROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件
理解:
它的嵌套逻辑类似我们使用的for循环
语法说明:
可以使用ON子句指定额外的连接条件
这个连接条件是与其他条件分开的
ON子句使语句具有更高的易读性
关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的,都表示内连接
1.3.1内连接(INNER JOIN)的实现
语法:
select 字段列表
from A表 INNER JOIN B表
ON 关联条件
where 等其他子句
特点:
只要在一条合并记录中,A表或者B表的数据为NULL,此时结果集就会抛弃这条记录
举例: ![在这里插入图片描述](https://img-blog.csdnimg.cn/89ccf102790149d5b40a66c47d85c29c.png)
1.3.2外连接(OUTER JOIN)的实现
左外连接(LEFT OUTER JOIN)
语法:
select 字段列表
From A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;
特点:左外连接的A表然如果在连接时候发现B表某条记录是NULL的话也是会将该条记录加入到结果集中。
举例: ![在这里插入图片描述](https://img-blog.csdnimg.cn/dcd89228eb9f492690479ae6564eea3d.png)
右外连接(LEFT OUTER JOIN)
语法:
select 字段列表
From A表 right join B表
ON 关联条件
where 等其他子句
特点:
当B表查询记录时,A表位null的记录也会加入到结果集中
其实左外连接和右外连接时相互的,只是驱动表和被驱动表互换了!
举例: ![在这里插入图片描述](https://img-blog.csdnimg.cn/d2fbdc0db4b848b292c0a82052548136.png)
满外连接(FULL OUTER JOIN)
说明:MYSQL不支持!!!
满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT join代替。
1.4-UNION的使用
合并查询结果利用UNION关键字。可以给出多条Select语句,并将它们的结果组合成单个结果集。
合并前提:
两个表对应的列数和数据类型必须相同,并且相互对应。
语法格式:
select column,...from table1
UNION[ALL]
select column,...from table2
UNION解析:
UNION操作符返回的是两个查询的结果集的并集,然后会去除重复的记录。
举例: UNION ALL解析: UNION ALL操作符返回的是两个查询结果的并集,但是不去重。 举例: 结论:因为union会自动去重,所以开销会比较大,推荐尽量使用union all,在合并之前尽量不要有重复的数据。
1.5- 7种SQL JOINS的实现
简单介绍如图: 举例实现: 1、中图:内连接: A∩B 代码:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
2、左上图:左外连接 代码:
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
3、右上图:右外连接 代码:
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
4、左中图:A - A∩B 代码:
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
5、右中图:B- A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
6、左下图=(左上+右中)或(右上+左中) 代码:
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
![在这里插入图片描述](https://img-blog.csdnimg.cn/99afcc1d1e95487aae6b5b983ba26806.png)
7、右下图=右中+左中 代码:
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
![在这里插入图片描述](https://img-blog.csdnimg.cn/1f7dd1293d79492fb7ab4e6346fc9d79.png)
1.5-SQL99语法新特性
1、自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ,然后进行等值连接 。
2、USING连接
当我们进行连接的时候,SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如:
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
表连接的约束条件可以有三种方式WHERE, ON, USING WHERE:适用于所有关联查询 ON :只能和JOIN一起使用,只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写,但分开写可读性更好。 USING:只能和JOIN一起使用,而且要求两个关联字段在关联表中名称一致,而且只能表示关联字段值相等
|