多表查询:数据来源不是同一张表,而是来源于多张表
通过有关系的字段,建立等值链接,进行多表查询
等值链接
限制:创建等值链接的列,列名相同,数据类型相同
select ename,dname from emp,dept; //因为没有建立等值链接,这只是一个笛卡尔集【列*列】
select ename ,dname from emp e ,dept d where e.deptno=d.deptno; //只是显示时没有笛卡尔集但实际上还是有笛卡尔集
查找共有的列【相同的列必须跟表的前缀】
select ename ,dname,e.deptno from emp e ,dept d where e.deptno=d.deptno;
select ename ,dname,d.deptno from emp e ,dept d where e.deptno=d.deptno;
步骤
1.确定数据来源【需要的数据来自哪些表】
2.找有关系的字段建立表与表之间的等值链接
3.书写查询语句
自然链接 natural join
遵循sql99的标准
限制:创建等值链接的列,列名相同,数据类型相同
select ename,dname from emp natural join dept;
查找共有的列【相同的列不能跟表的前缀】
select e.ename,d.dname,deptno from emp e natural join dept d;
join using()
join 要链接的表()里面要跟上创建等值链接的列
限制:创建等值链接的列,列名相同,数据类型可以不同
select ename,dname from emp join dept using(deptno);
查找共有的列【相同的列不能跟表的前缀】
select ename,dname,deptno from emp join dept using(deptno);
join on()
on()跟的是等值链接的条件
限制:创建等值链接的列,列名可以不同,数据类型可以不同,只要数据之间有关系
select ename,sal,grade from emp join salgrade on(sal between losal and hisal);
select dname,ename,sal,grade from emp natural join dept join salgrade on(sal between losal and hisal);
select dname,ename,sal,grade from emp join dept using(deptno) join salgrade on(sal between losal and hisal);
select dname,ename,sal,grade from emp e , dept d, where e.deptno=d.deptno and sal between losal and hisal;
set
union---将两个或者多个结果合并在一起,去除重复的行,排序
union all --将两个或多个结果合并在一起,不去重,不排序 //项目中建议使用union all 可以提高性能
union 例子去重,排序
SQL> select ename ,job,deptno from emp where deptno in (10,20)
2 union
3 select ename ,job,deptno from emp where deptno in (10,30);
ENAME JOB DEPTNO
---------- --------- ----------
ADAMS CLERK 20
ALLEN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
FORD ANALYST 20
JAMES CLERK 30
JONES MANAGER 20
KING PRESIDENT 10
MARTIN SALESMAN 30
MILLER CLERK 10
SCOTT ANALYST 20
SMITH CLERK 20
TURNER SALESMAN 30
WARD SALESMAN 30
14 rows selected.
union all 例子 不去重,不排序
SQL> select ename ,job,deptno from emp where deptno in (10,20)
2 union all
3 select ename ,job,deptno from emp where deptno in (10,30);
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
JONES MANAGER 20
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
ADAMS CLERK 20
FORD ANALYST 20
MILLER CLERK 10
ALLEN SALESMAN 30
WARD SALESMAN 30
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
KING PRESIDENT 10
TURNER SALESMAN 30
JAMES CLERK 30
MILLER CLERK 10
17 rows selected.
嵌套
SQL> select ename ,job,deptno from emp where deptno in (10)
2 union
3 select ename ,job,deptno from emp where deptno in (10,20)
4 union all
5 select ename ,job,deptno from emp where deptno in (30);
ENAME JOB DEPTNO
---------- --------- ----------
ADAMS CLERK 20
CLARK MANAGER 10
FORD ANALYST 20
JONES MANAGER 20
KING PRESIDENT 10
MILLER CLERK 10
SCOTT ANALYST 20
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
MARTIN SALESMAN 30
BLAKE MANAGER 30
TURNER SALESMAN 30
JAMES CLERK 30
14 rows selected.
intersect 交集
SQL> select deptno from emp where deptno in (10,30)
2 intersect
3 select deptno from emp where deptno in (10,20);
DEPTNO
----------
10
minus 差集,第一个减去第二个 //用来计算列中是否存在相同的
SQL> select deptno from emp where deptno in (10)
2 minus
3 select deptno from emp where deptno in (10,30);
no rows selected
SQL>