1 笛卡尔乘积 cross join
select ename ,dname from emp cross join dept;
2 等值连接
旧语法:
select ename ,dname from emp ,dept where emp.deptno = dept . deptno;
新语法:
where 语句中 用于描述 过滤条件 , 连接条件不再使用where 语句 .
select ename,dname from emp join dept on (emp.deptno=dept.deptno);
等值连接的简洁写法:
select ename ,dname from emp join dept using (deptno);
3 不等值连接
select ename,dname from emp e join salgrade s on(e.sal between s.losal and s.hisal);
4三个表的连接
select ename,dname,grade from
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal)
where ename not like '_A%' ;
5 自连接
select e1.ename ,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno ) ;
6 外连接:
左外连接
select e1.ename ,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno ) ;
e1 中 未被连接的ename 项 king 也显示了出来
右外连接:
select e1.ename ,e2.ename from emp e1 right join emp e2 on (e1.mgr = e2.empno ) ;
全外连接:
select e1.ename ,e2.ename from emp e1 full join emp e2 on (e1.mgr = e2.empno )