1 等值连接
select e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno=d.deptno;
2 不等值连接
select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
3 外连接
select d.deptno, d.dname, count(e.empno)
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno,d.dname;
通过外连接可以将连接条件不成立的记录仍然包含在最后的结果中
- 左外连接
- 右外连接 保留右边表的内容
select d.deptno, d.dname, count(e.empno)
from emp e right outer join dept d
on (e.deptno=d.deptno)
group by d.deptno,d.dname;
4 自连接
通过表的别名将同一张表视为多张表
select e.ename, b.ename
from emp e, emp b
where e.mgr=b.empno;