sql基本语句（三）表连接查询语句
1表的连接
我们要从多张表中查询信息，就得以一定的条件将表连接在一起查询。
1）cartesian（笛卡尔）连接
当多张表在一起查询时，没有给定正确的连接条件，结果是第一张表的所有行和第二张表的所有行进行矩阵相乘，得到n*m行的结果集
一般来说笛卡尔连接连接不是我们需要的结果，但表如果在一行的情况下，结果有可能正确。
scott@TEST>select ename,dname from emp,dept; ENAME DNAME -------------------- ---------------------------- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING SMITH RESEARCH ALLEN RESEARCH WARD RESEARCH JONES RESEARCH MARTIN RESEARCH BLAKE RESEARCH CLARK RESEARCH SCOTT RESEARCH KING RESEARCH TURNER RESEARCH ADAMS RESEARCH JAMES RESEARCH FORD RESEARCH MILLER RESEARCH SMITH SALES ALLEN SALES WARD SALES JONES SALES MARTIN SALES BLAKE SALES CLARK SALES SCOTT SALES KING SALES TURNER SALES ADAMS SALES JAMES SALES FORD SALES MILLER SALES SMITH OPERATIONS ALLEN OPERATIONS WARD OPERATIONS JONES OPERATIONS MARTIN OPERATIONS BLAKE OPERATIONS CLARK OPERATIONS SCOTT OPERATIONS KING OPERATIONS TURNER OPERATIONS ADAMS OPERATIONS JAMES OPERATIONS FORD OPERATIONS MILLER OPERATIONS 56 rows selected.
2）等值连接
在连接中给定一个相等连接条件
scott@TEST>select ename,dname from emp,dept where emp.deptno=dept.deptno; ENAME DNAME -------------------- ---------------------------- CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING JONES RESEARCH FORD RESEARCH ADAMS RESEARCH SMITH RESEARCH SCOTT RESEARCH WARD SALES TURNER SALES ALLEN SALES JAMES SALES BLAKE SALES MARTIN SALES 14 rows selected.
3）不等连接
连接条件不是一个相等的条件
scott@TEST>select ename,sal,grade from emp,salgrade where sal between losal and hisal; ENAME SAL GRADE -------------------- ---------- ---------- SMITH 800 1 JAMES 950 1 ADAMS 1100 1 WARD 1250 2 MARTIN 1250 2 MILLER 1300 2 TURNER 1500 3 ALLEN 1600 3 CLARK 2450 4 BLAKE 2850 4 JONES 2975 4 SCOTT 3000 4 FORD 3000 4 KING 5000 5 14 rows selected.
4）外键连接
将一张表有，而另一张表没有的行也显示出来
+号的意思为将没有员工的部门，用null来匹配
+号不能同时放在等号的两边，只能出现在一边
scott@TEST>select ename,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno; ENAME DNAME DEPTNO -------------------- ---------------------------- ---------- CLARK ACCOUNTING 10 KING ACCOUNTING 10 MILLER ACCOUNTING 10 JONES RESEARCH 20 FORD RESEARCH 20 ADAMS RESEARCH 20 SMITH RESEARCH 20 SCOTT RESEARCH 20 WARD SALES 30 TURNER SALES 30 ALLEN SALES 30 JAMES SALES 30 BLAKE SALES 30 MARTIN SALES 30 14 rows selected. scott@TEST>select ename,dname,emp.deptno from emp,dept where emp.deptno(+)=dept.deptno; ENAME DNAME DEPTNO -------------------- ---------------------------- ---------- CLARK ACCOUNTING 10 KING ACCOUNTING 10 MILLER ACCOUNTING 10 JONES RESEARCH 20 FORD RESEARCH 20 ADAMS RESEARCH 20 SMITH RESEARCH 20 SCOTT RESEARCH 20 WARD SALES 30 TURNER SALES 30 ALLEN SALES 30 JAMES SALES 30 BLAKE SALES 30 MARTIN SALES 30 OPERATIONS 15 rows selected.
5）自连接
标的一列和同一个表的另一个列作为连接的条件
最后一个ename king 没有上级，这地方左边列的null和他是匹配的
scott@TEST>select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+); ENAME ENAME -------------------- -------------------- FORD JONES SCOTT JONES JAMES BLAKE TURNER BLAKE MARTIN BLAKE WARD BLAKE ALLEN BLAKE MILLER CLARK ADAMS SCOTT CLARK KING BLAKE KING JONES KING SMITH FORD KING 14 rows selected.
2.sql99规则的表连接操作；
1）cross join -->相当于笛卡尔连接
scott@TEST>select ename,dname from emp cross join dept; ENAME DNAME -------------------- ---------------------------- SMITH ACCOUNTING ALLEN ACCOUNTING WARD ACCOUNTING JONES ACCOUNTING MARTIN ACCOUNTING BLAKE ACCOUNTING CLARK ACCOUNTING SCOTT ACCOUNTING KING ACCOUNTING TURNER ACCOUNTING ADAMS ACCOUNTING JAMES ACCOUNTING FORD ACCOUNTING MILLER ACCOUNTING SMITH RESEARCH ALLEN RESEARCH WARD RESEARCH JONES RESEARCH MARTIN RESEARCH BLAKE RESEARCH CLARK RESEARCH SCOTT RESEARCH KING RESEARCH TURNER RESEARCH ADAMS RESEARCH JAMES RESEARCH FORD RESEARCH MILLER RESEARCH SMITH SALES ALLEN SALES WARD SALES JONES SALES MARTIN SALES BLAKE SALES CLARK SALES SCOTT SALES KING SALES TURNER SALES ADAMS SALES JAMES SALES FORD SALES MILLER SALES SMITH OPERATIONS ALLEN OPERATIONS WARD OPERATIONS JONES OPERATIONS MARTIN OPERATIONS BLAKE OPERATIONS CLARK OPERATIONS SCOTT OPERATIONS KING OPERATIONS TURNER OPERATIONS ADAMS OPERATIONS JAMES OPERATIONS FORD OPERATIONS MILLER OPERATIONS 56 rows selected.
2）natural join 自然连接 ->等值连接 用于两张表有一个同名的列
scott@TEST>select ename,dname from emp natural join dept; ENAME DNAME -------------------- ---------------------------- CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING JONES RESEARCH FORD RESEARCH ADAMS RESEARCH SMITH RESEARCH SCOTT RESEARCH WARD SALES TURNER SALES ALLEN SALES JAMES SALES BLAKE SALES MARTIN SALES 14 rows selected.
3）join ... using 自定义关联连接，用于两张表有多个同名的列
scott@TEST>select ename,dname from emp join dept using(deptno); ENAME DNAME -------------------- ---------------------------- CLARK ACCOUNTING KING ACCOUNTING MILLER ACCOUNTING JONES RESEARCH FORD RESEARCH ADAMS RESEARCH SMITH RESEARCH SCOTT RESEARCH WARD SALES TURNER SALES ALLEN SALES JAMES SALES BLAKE SALES MARTIN SALES 14 rows selected.
