sql基础篇-多表操作

------左连接--------- 全称left outer join 简写left join 左为主表 右为从表就是副表意思 on后面表连接列
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A
LEFT JOIN DEPT B
ON A.DEPTNO = B.DEPTNO

------左连接另一种语法---------带(+)的表条件是从表 不带是主表
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A,DEPT B
WHERE A.DEPTNO = B.DEPTNO(+)

SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A,DEPT B
WHERE B.DEPTNO = A.DEPTNO(+) --B.DEPTNO = A.DEPTNO(+) 换过来就是dept主表了 运行看看对比效果

–右连接了解就行 用左关联是一样的表顺序换一下就行
–右边是主表 左边是从表 工作用左关联就行一样的 只是表顺序不一样调过来就是一样的效果了 比如上面例子
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A
RIGHT JOIN DEPT B
ON A.DEPTNO = B.DEPTNO

------full join全连接------全称 full outer join
SELECT *
FROM EMP A
FULL JOIN DEPT B
ON A.DEPTNO = B.DEPTNO --看下效果两个表不匹配数据都显示一般工作不用

-------内连接------- 表1,表2 where 后面表的关联条件
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A,
DEPT B
WHERE A.DEPTNO = B.DEPTNO

---------inner join 内连接------------ 默认join 可以省略inner on后面表的关联条件
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A
JOIN DEPT B
on A.DEPTNO = B.DEPTNO

------in-------只显示emp主表数据 半连接 in子查询里面不用带表连接列
SELECT A.EMPNO,
A.ENAME,
A.JOB
FROM EMP A
WHERE A.DEPTNO IN (SELECT DEPTNO
FROM DEPT)

-----exists------只显示emp主表数据 半连接 exists子查询里面带表连接列 直接现在子查询里面匹配好数据
SELECT A.EMPNO,
A.ENAME,
A.JOB
FROM EMP A
WHERE EXISTS (SELECT 1 /select 1 这里1是固定语法 null 1 什么都可以 可以试试是不是一样的结果/
FROM DEPT B
WHERE A.DEPTNO = B.DEPTNO)

–not in 和 not exist 反连接就是不是在not in， not exists里面的关联列数据

SELECT A.EMPNO,
A.ENAME,
A.JOB
FROM EMP A
WHERE A.DEPTNO NOT IN (SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = ‘10’)
------------------where not exists,和 where 列 not in(select 列)区别
NOT IN A.DEPTNO NOT IN (SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = ‘10’) 在外面匹配子查询里面数据

NOT EXISTS 是在子查询里面做匹配
(SELECT NULL
FROM DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND B.DEPTNO = ‘10’)

SELECT A.EMPNO,
A.ENAME,
A.JOB
FROM EMP A
WHERE NOT EXISTS (SELECT NULL
FROM DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND B.DEPTNO = ‘10’)

EXISTS适合于外表小而内表大的情况，IN适合于外表大而内表小的情况”。EXISTS是对外表做
LOOP循环，在循环体中执行内表查询，所以循环的次数越少，速度就越快。IN是把外表和内表做
hash连接，所以内表越小，整个查询的范围就小，效率就高。

–union 和 union all 去重 不去重的区别

SELECT * FROM emp --去重了14条 注意表字段类型一一对应
UNION
SELECT * FROM emp;

SELECT * FROM emp --不去重28条 注意表字段类型一一对应
UNION ALL
SELECT * FROM emp;

–比如这个会报错
SELECT ename FROM emp --一个字符串类型 一个数值类型 报ora_01790错误
UNION
SELECT empno FROM emp;

SELECT ename,empno FROM emp --列块数要一致 错误
UNION
SELECT empno FROM emp;

---

所有内外连接 半连接 反连接都可以在 on的条件后面 加过滤条件 AND OR
后面牵扯到连接转换注意过滤条件

比如左连接从表带where过滤条件变成内连接只关联匹配数据 需要注意
因为 查询转换器将很多复杂的sql进行等价转换
SELECT A.EMPNO,
A.ENAME,
A.JOB,
B.DNAME
FROM EMP A
LEFT JOIN DEPT B
ON A.DEPTNO = B.DEPTNO
WHERE b.deptno = ‘10’

–比如

select * from emp a inner join dept b on a.deptno=b.deptno and b.deptno=20
等价于
select * from emp a inner join dept b on a.deptno=b.deptno where b.deptno=20

select * from emp a LEFT join dept b on a.deptno=b.deptno where b.deptno=20
–转换成上面内连接了 where b.deptno=20 从表过滤条件