一.笛卡尔积
a.oracle写法
scott@ORCL> select count(1) from emp;
COUNT(1)
----------
14
scott@ORCL> select count(1) from dept;
COUNT(1)
----------
4
scott@ORCL> select count(1) from emp,dept;
COUNT(1)
----------
56
b.传统sql写法
scott@ORCL> select count(1) from emp cross join dept;
COUNT(1)
----------
56
二.等值连接(也叫作内连接)
a.oracle写法
scott@ORCL> select count(1) from emp a,dept b where a.deptno=b.deptno;
COUNT(1)
----------
14
b.传统sql写法
scott@ORCL> select * from emp a join dept b on a.deptno=b.deptno;
else1:等值连接的记录筛选
a.
scott@ORCL> select * from emp a,dept b where a.deptno=b.deptno and a.deptno=30;
b.
scott@ORCL> select * from emp a join dept b on a.deptno=b.deptno where a.deptno=30;
else2:不等值连接
a.
scott@ORCL> select * from emp a,salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
b.
scott@ORCL> select * from emp a join salgrade b on a.sal between b.losal and b.hisal;
三.外连接
1.左外连接(left outer join)
a.
scott@ORCL> select * from emp a,dept b where a.deptno=b.deptno(+);
b.
scott@ORCL> select * from emp a left outer join dept b on a.deptno=b.deptno;
2.右外连接
a.
scott@ORCL> select * from emp a,dept b where a.deptno(+)=b.deptno;
b.
scott@ORCL> select * from emp a right outer join dept b on a.deptno=b.deptno;
3.全外连接
a.
scott@ORCL> select * from emp a,dept b where a.deptno(+)=b.deptno union
2 select * from emp a,dept b where a.deptno=b.deptno(+);
b.
scott@ORCL> select * from emp a full outer join dept b on a.deptno=b.deptno;
四.自然连接:根据两个表的相同字段
scott@ORCL> select * from emp natural join dept b;
如果两张表没有相同字段会报错:没有选定行
五.using字句:指定表的某个字段作为连接的字段
scott@ORCL> select * from emp a join dept b using(deptno);