1.等值连接
查询部门10中经理的员工编号、姓名、部门编号、部门名称及年薪
select empno,ename,e.deptno,dname,12*sal+nvl(comm,0)*12 from emp e,dept d where e.deptno=d.deptno and
d.deptno=10 and job='MANAGER';
查询每个部门经理的员工编号、姓名、工资、部门编号和部门名称
select empno,ename,sal,d.deptno,dname
from emp e,dept d
where e.deptno = d.deptno
and job = 'MANAGER';
结果为:
EMPNO ENAME SAL DEPTNO DNAME
----- ------ ------- ------ ----------
7782 CLARK 2450.00 10 ACCOUNTING
7566 JONES 2975.00 20 RESEARCH
7698 BLAKE 2850.00 30 SALES
2.不等值连接
查询每个员工的姓名、工资及工资等级
select ename, sal, grade
from emp e, salgrade s
where sal between losal and hisal;
3.内连接--匹配的记录查询出来
语法:inner join 表名 on 条件
查询部门20中员工编号、姓名、部门编号和部门名称
select empno, ename, e.deptno, dname
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 20;
<==>
select empno, ename, e.deptno, dname
from emp e inner join dept d
on e.deptno = d.deptno
where e.deptno = 20;
4.外连接: