Oracle联合查询详解

联合查询（多表连接）
概念：多张表连接，合并查询数据

分类：
1.笛卡尔积连接（交叉连接）
语法：
select t1.column1,t1.column2,t2.column1,t2.column2,… from table_name1 t1 cross join table_name2 t2;
select t1.col_list,t2.col_list from table_name1 t1,table_name2 t2;

关键字：cross join

select * from emp t1 cross join dept t2;

数据量：是两张表的数据的乘积

2.内连接
语法：
select t1.col_list,t2.col_list from table_name1 t1 [inner] join table_name2 t2 on t1.column1 = t2.column2;

关键字：inner join … on …

select t1.empno,t1.ename,t1.deptno,t2.deptno,t2.dname from emp t1 join dept t2 on t1.deptno = t2.deptno;

数据量：两张表所关联的数据的条数

3.自然连接（不常用）
语法：
select t1.col_list,t2.col_list from table_name1 t1 natural join table_name2 t2;

关键字：natural join

select * from emp t1 natural join dept t2;

学生表 老师表
id id
teacher_id

数据量：自动根据相同字段相关联，获取相关条件的数据

4.外连接
1）全外连接
语法：
select t1.col_list,t2.col_list from table_name1 t1 full [outer] join table_nema2 t2 on t1.column1 = t2.column2;

关键字：full [outer] join ... on ...select t1.*,t2.* from emp t1 full join dept t2 on t1.deptno = t2.deptno;数据量：相关联的数据全部展示，并左表数据全部展示，右表数据不匹配，以空值显示，右表数据全部展示，左表不匹配，以空值显示

2）左外连接
语法：
select t1.col_list,t2.col_list from table1 t1 left [outer] join table2 t2 on t1.column1 = t2.column2;
select t1.col_list,t2.col_list from table1 t1,table2 t2 where t1.column1 = t2.column2(+)

关键字：left [outer] join … on …

select * from emp t1 left join dept t2 on t1.deptno = t2.deptno;

数据量：左表数据全部展示，右表数据不匹配，以空值显示

3）右外连接
语法：
select t1.col_list,t2.col_list from table1 t1 right [outer] join table2 t2 on t1.column1 = t2.column2;
select t1.col_list,t2.col_list from table1 t1,table2 t2 where t1.column1(+) = t2.column2;

关键字：right [outer] join … on …

select * from emp t1 right join dept t2 on t1.deptno = t2.deptno;

数据量：右表数据全部展示，左表数据不匹配，以空值显示

5.自连接
自连接是一种特殊的连接，自己和自己连接
select t1.col_list,t2.col_list from table1 t1 join table1 t2 t1.column1 = t2.column2;

自连接关键字：可以是任何连接方式

–查询emp表中自己的领导
select t1.empno 员工编号,t1.ename 员工姓名,t1.mgr 领导编号,t2.empno “领导编号(empno)”,t2.ename 领导姓名 from emp t1 join emp t2 on t1.mgr = t2.empno;

6.不等连接
语法：
select t1.col_list,t2.col_list from table1 t1 join table2 t2 on t1.column1 != t2.column2;

select * from emp;
select * from dept;
select * from salgrade;

select *
from emp t1
join dept t2 on t1.deptno = t2.deptno
join salgrade t3 on t1.sal between t3.losal and t3.hisal;