多表查询是从多个表中获取数据。
表连接的语法:
select table1.col,table2.col
from table1,table2
where table1.col=table2.col
连接类型
1.自然连接:
natural join子句
using子句
on子句
会以两个表中具有相同名字的列为条件创建等值连接;在表中查询满足等值条件的数据;如果只是列名相同而数据类型不同,则会产生错误。
比如两个表中会有两个同名的列,如果存在其他公共列,连接就会全部使用他们。
2.外链接
查询检索emp表中的所有行,即使dept中没有。
left outer join(左外连接)
right outer join(右外链接)
使用外链接来查出一个表中不匹配连接条件的行,外链接符号是(+)
full outer join(全外连接)
查询员工对应的部门信息,包括没有部门的员工和没有员工的部门。
两个表中在连接过程中只返回匹配的行成为内连接;
两个表在连接过程中除了返回满足条件的行以外,还返回左或右表中不满足条件的行,称为外链接。
两个表在连接过程除了返回满足条件的行以外,还返回两个表中不满足条件的行,称为全外连接。
3.交叉连接
限制重复的列名
在多表中使用表前缀限制列名;
使用表前缀可以提高效率;
使用表别名代替全表明前缀;
表别名提供一个较短的名称:使sql代码量更少,使用较少的内存;
在不同表中具有相同列明的列可以用别名加以区分。
select e.ename,e.deptno,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno
同一列名多表使用时要写上表名做前缀。
把两个表连接,每个列的名称的前缀都是表,那么e.ename就是从emp表里的ename列,以此类推。下面需要加一个条件-表可以连接的条件。emp表里的deptno和dept表里的deptno是一样的,所以可以连接在一起。
SQL> select e.ename,e.deptno,d.deptno,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK 10 10 ACCOUNTING
KING 10 10 ACCOUNTING
MILLER 10 10 ACCOUNTING
JONES 20 20 RESEARCH
FORD 20 20 RESEARCH
ADAMS 20 20 RESEARCH
SMITH 20 20 RESEARCH
SCOTT 20 20 RESEARCH
WARD 30 30 SALES
TURNER 30 30 SALES
ALLEN 30 30 SALES
ENAME DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
JAMES 30 30 SALES
BLAKE 30 30 SALES
MARTIN 30 30 SALES
14 rows selected.
SQL> select e.ename , e.job , e.sal , d.deptno
2 from emp e, dept d
3 where e.deptno=d.deptno;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20
ALLEN SALESMAN 1600 30
WARD SALESMAN 1250 30
JONES MANAGER 2975 20
MARTIN SALESMAN 1250 30
BLAKE MANAGER 2850 30
CLARK MANAGER 2450 10
SCOTT ANALYST 3000 20
KING PRESIDENT 5000 10
TURNER SALESMAN 1500 30
ADAMS CLERK 1100 20
JAMES CLERK 950 30
FORD ANALYST 3000 20
MILLER CLERK 1300 10
14 rows selected.
除了两个表的连接条件,还有dname=sales的条件,二者需要同时满足。否则会产生很多个值(或者笛卡尔积),两个表的各行相乘,那么会输出很多数据。
所以条件一定要写的充足,否则数据很多。
SQL> select e.ename , e.job , e.sal , d.deptno
2 from emp e, dept d
3 where e.deptno=d.deptno
4 and d.dname='SALES';
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
WARD SALESMAN 1250 30
TURNER SALESMAN 1500 30
ALLEN SALESMAN 1600 30
JAMES CLERK 950 30
BLAKE MANAGER 2850 30
MARTIN SALESMAN 1250 30
6 rows selected.
表自连接:表自己进行连接。
需要满足条件:表里有从属关系。谁是谁的员工,谁是谁的老板,如果出现编号,那么他们就具有从属关系。
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno;
非等值连接
如果两个表中没有相等的列名或者值,那么可以用非等值连接,如工资可以使用范围。用between 。。。and。。。连接起来。然后在分配员工级别。
多表查询
连接多个表,几个表中有相关关系。