大数据学习从入门到精通（day4 集合、联合查询）

一、集合

集合的定义： 每一个SQL查询的结果就是一个集合。

关键词：

并集：union、union all 取两个集合（查询结果）中所有的元素

两者的区别：union all 不会将相同的数据合并，而union会将查询的结果排序删除重复的元素，因此union的效率不如union all 但是在实际的运用中要选择合适的关键词进行查询。

交集：intersect 取两个集合（查询结果）中相同的元素

差集：minus 取第一个集合减去两个集合中相同部分的元素

举例：

并集
SELECT * FROM EMP WHERE SAL>1000
UNION ALL
SELECT * FROM EMP WHERE SAL>2000;

SELECT * FROM EMP WHERE SAL>1000
UNION
SELECT * FROM EMP WHERE SAL>2000;

交集
SELECT * FROM EMP WHERE SAL>2000
INTERSECT
SELECT * FROM EMP WHERE SAL<2500;

SELECT * FROM EMP WHERE SAL>2000 AND SAL<2500;

差集
SELECT * FROM EMP WHERE SAL>1000
MINUS
SELECT * FROM EMP WHERE SAL>2000;

 使用这些关键词时，第一条查询结尾处不加“；”第二条查询结尾处一定要加“；”

二、联合查询

交叉连接（笛卡尔积连接）、内连接、自连接、外连接（全连接、左外连接、右外连接）、自然连接。

1.交叉连接（笛卡尔积连接）：

将两个表中的每一行数据进行连接并输出，数据的数量是两个表行数的乘积。

语法：select 列名,列名,...from a表  cross join b表;

举例：

SELECT e.*,d.deptno dno,d.dname,d.loc FROM EMP e CROSS JOIN DEPT D;

SELECT e.*,d.deptno dno,d.dname,d.loc FROM EMP e,DEPT D;

/*cross join可以省略*/

 2.内连接

将两个符合条件的数据连接到一起，不符合条件的不会显示。

语法：select 列名,...,列名 from a表  inner join b表  on 连接条件;

举例：

SELECT * FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO;
/*INNER 可以省略不写*/

使用内连接时如果有一条数据他的连接条件中的字段为null，那么这条数据不会产生连接，查询结果中不会显示该条数据，这样从某种程度上来说使用内连接查询会导致数据丢失的情况。

3.自连接

自连接是将表自身相连接

语法：select 列名,...,列名 from a表 join a表  on 连接条件;

举例：

SELECT e1.*,e2.ENAME FROM EMP e1 JOIN EMP e2 ON e1.MGR=e2.EMPNO;
/*将emp表中mgr字段和empno字段连接*/

4.不等连接

当连接条件是不等条件时使用不等连接，通常确定一个值的属于的范围、等级等。

举例：

SELECT * FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;
/*确定emp表中员工的sal在salgrade表中的等级*/

5.全连接

全连接会查出两个表中所有的数据，如果两个表中的数据都符合连接条件，那么它会和内连接一样，将符合条件的数据连接为一条记录，如果第一表中的一条数据，在第二张表中没有找到和它对应的记录（第二张表没有和第一张表这条数据符合连接条件）， 那么它同样会显示第一张表的这条记录，同时它对应的第二张表数据的位置会显示为空。

语法：

select 列名,列名,列名,....from 表A full outer join 表B on 连接条件;

举例：

SELECT * FROM EMP e FULL JOIN DEPT d on e.DEPTNO=d.DEPTNO;
/*全连接查询可以同时兼顾到两个表，不会有数据丢失*/

6.左外连接和右外连接

左外连接：会根据左表的所有数据去连接右表对应的数据，如果左表的数据在右表查询不到就会显示null；

关键词：left join

右外连接：与左外连接相反，根据右表的数据连接左表，查询不到显示null；

关键词：right join

SELECT * FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

SELECT * FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

7.自然连接

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

语法：
select col_name,col_name,...from table_a natural join table_b;
   
select col_name,....from table_a join table_b using(关系列)
select * from emp natural join dept;        --自然连接
select * from emp join dept using(deptno);  --自然连接using用来指定关系列