连接查询和子查询小结

1、什么是子查询？举个简单的例子，那么子查询有什么优劣呢？

子查询 (内查询) 在主查询之前一次执行完成。
子查询的结果被主查询(外查询)使用 。
可以用一个子查询替代上边的的表名。
子查询，将查询操作嵌套在另一个查询操作中。先执行子查询，再执行外查询

注：在查询时基于未知的值时，应使用子查询 子查询可以返回多个结果/单个结果，结果个数不同应该使用不同的操作符

通过子查询不难看出，可以根据employee_id查到department_id,然后根据deparment_id查到location_id然后查city字段就行了（参见上图）

//根据上述表查询emloyee_id的所在城市是长沙的（相当于就是无限套娃）

SELECT employee_id FROM employees WHERE department_id IN (
    
    SELECT  department_id FROM departments WHERE location_id IN(
        
        SELECT location_id FROM location WHERE city='长沙')）

2、什么是连接查询呢？又有多少中连接查询？其中又各有什么优劣呢？连接查询主要分为三种:内连接、外连接、交叉连接。

内连接 INNER JOIN

内连接(INNER JOIN)，返回连接表中符合连接条件和查询条件的数据行。(所谓的链接表就是数据库在做查询形成的中间表)。

SELECT a.*, b.* FROM employees a INNER JOIN departments b ON a.department_id=b.department_id

外连接（外连接分为左外连接、右外链接、全外连接）

​ 外连接分为左外连接、右外链接、全外连接三种。从保证某个表的数据的完整性来说的话，LEFT JOIN 左外连接，保证左表的完整性，RIGHT JOIN 右外连接，保证右表的完整性

（1)左外连接LEFT JOIN或LEFT OUTER JOIN

左外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

SELECT a.* FROM employees a LEFT JOIN departments b ON b.department_id=a.department_id

注：查询结果，会将左表全部查询出来，如果未匹配到连接（b表中未找到和a表中的department_id相等的字段），则右表为null。

（2)右外链接RIGHT JOIN 或 RIGHT OUTER JOIN

右外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

SELECT a.* FROM employees a RIGHT JOIN departments b ON b.department_id=a.department_id

注：查询结果，会将左表全部查询出来，如果未匹配到连接（a表中未找到和b表中的department_id相等的字段），则左表为null。

（3)全外连接（全连接）FULL JOIN 或 FULL OUTER JOIN

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

FULL JOIN的基本语法如下：

oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。

//oracle写法
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

//mysql写法
SELECT a.* FROM employees  a LEFT JOIN departments b ON a.department_id = b.department_id UNION 
 SELECT a.* FROM employees  a RIGHT JOIN departments b ON a.department_id = b.department_id

等值连接和自然连接

1.等值连接（相等连接）：

使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。

2.自然连接：

等值连接中去掉重复的列，形成的连接。说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示(ORACLE环境下测试的)。对于每种连接类型(除了交叉连接外)，均可指定NATURAL。

数据库应用中最常用的是“自然连接”，它在目标列中去除相同的字段名。进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。

等值连接和自然连接的区别：

1）等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。

2）等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。

3、连接查询和子查询哪个效率高呢？
​ 首先两者不存在谁优于谁的说法，只是那种更适应某种环境。一般要看你是什么用途，如果数据量少的话可以子查询，或者经常用的数据就使用子查询，不经常用的就连接查询，适习惯而定，当然是指数据量少的情况下。

​ 一般来讲连接查询效率更高，因为子查询会多次遍历数据，而连接查询只遍历一次，但是如果数据量较少的话子查询更加容易控制。但如果数据量大的话两者的区别就会很明显，对于数据量多的肯定是用连接查询快些，原因：因为子查询会多次遍历所有的数据（视你的子查询的层次而定），如果你的子查询是在无限套娃，且每张表数据量不大，使用子查询效率高。

​ 连接查询只会遍历一次，但是数据量少的话也就无所谓是连接查询还是子查询，多表数据量大建议采用连接查询。

注：连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：

1、 查两表关联列相等的数据用内连接。

2、 左表是右表的子集时用右外连接。

3、 右表是左表的子集时用左外连接。

4、 左表和右表彼此有交集但彼此互不为子集时候用全外连接（全连接）。

5、 求差操作的时候用联合查询。

————————————————
版权声明：本文为CSDN博主「zhsh666y」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44041590/article/details/114343328