1、隐式内连接
(1)等值内连接:
select * from emp,dept where emp.deptno = dept.deptno;
(2)不等值内连接:
select * from emp,dept where emp.deptno <> dept.deptno;
(3)自连接:自己连接自己
- 查询员工编号,员工姓名,经理的编号,经理的姓名
在emp表当中有这样的对应关系
select e1.empno,e1.ename,e1.mgr,m1.ename from emp e1,emp m1 where e1.mgr = m1.empno;
- 查询员工编号,员工姓名,员工部门名称,经理的编号,经理的姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1,emp m1,dept d1 where e1.mgr = m1.empno and e1.deptno = d1.deptno;
- 查询员工编号,员工姓名,员工部门名称,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1,emp m1,dept d1,dept d2
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
- 查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称
工资等级表
select * from salgrade;
select e1.empno,e1.ename,d1.dname,e1.sal,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1,emp m1,dept d1,dept d2,salgrade s1
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal;
- 查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
select e1.empno,e1.ename,d1.dname,e1.sal,s1.grade,e1.mgr,m1.ename,d2.dname,m1.sal,s2.grade
from emp e1,emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where e1.mgr = m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal;
- 查询员工编号,员工姓名,员工部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级,将工资等级数字1,2,3,4,显示