新语法
1.cross join的使用:对两表执行笛卡尔积(在生产环境中要尽量避免产生笛卡尔积)
SQL> SELECT e.DEPTNO,DNAME,LOC,ENAME ,SAL FROM EMP e CROSS JOIN DEPT d;
2.natural join 的使用:对两表中字段名相同的字段都做等值连接
相同的列名字和数据类型都要一样。
如果名字一样,数据类型不一样,就会发生错误。
SQL> SELECT DEPTNO,DNAME,LOC,ENAME ,SAL FROM EMP NATURAL JOIN DEPT;
DEPTNO DNAME LOC ENAME SAL
---------- -------------- ------------- ---------- ----------
20 RESEARCH DALLAS SMITH 800
30 SALES CHICAGO ALLEN 1600
30 SALES CHICAGO WARD 1250
20 RESEARCH DALLAS JONES 2975
SQL> SELECT ENAME,SAL ,DNAME,LOC FROM EMP E NATURAL JOIN DEPT D
2 WHERE DEPTNO IN (10,20);
ENAME SAL DNAME LOC
---------- ---------- -------------- -------------
SMITH 800 RESEARCH DALLAS
JONES 2975 RESEARCH DALLAS
SCOTT 3000 RESEARCH DALLAS
ADAMS 1100 RESEARCH DALLAS
FORD 3000 RESEARCH DALLAS
CLARK 2450 ACCOUNTING NEW YORK
KING 5000 ACCOUNTING NEW YORK
MILLER 1300 ACCOUNTING NEW YORK
已选择8行。
natural join中的公共字段前不能写表别名
SQL> SELECT ENAME,SAL,DNAME,LOC FROM EMP E NATURAL JOIN DEPT
2 WHERE E.DEPTNO IN (20,30);
WHERE E.DEPTNO IN (20,30)
*
ERROR 位于第 2 行:
ORA-25155: NATURAL 联接中使用的列不能有限定词
2.USING 子句的使用:(using后面跟的是等值连接的字段,using后面只能跟一个字段)
在using后面指定两张表相同的列。
SQL> SELECT ENAME,SAL,DNAME,LOC FROM EMP E JOIN DEPT D USING (DEPTNO)
2 WHERE DEPTNO=10;
ENAME SAL DNAME LOC
---------- ---------- -------------- -------------
CLARK 2450 ACCOUNTING NEW YORK
KING 5000 ACCOUNTING NEW YORK
MILLER 1300 ACCOUNTING NEW YORK
using连接中的公共字段前不能写表别名
SQL> SELECT ENAME,SAL,DNAME,LOC FROM EMP E JOIN DEPT D USING (DEPTNO)
2 WHERE D.DEPTNO=10;
WHERE D.DEPTNO=10
*
ERROR 位于第 2 行:
ORA-25154: USING 子句的列部分不能有限定词
SQL> select e.empno,e.name,d.deptno,d.dname,d.loc from emp e join dept d
2 using (deptno);
select e.empno,e.name,d.deptno,d.dname,d.loc from emp e join dept d
*
ERROR 位于第 1 行:
ORA-25154: USING 子句的列部分不能有限定词
正确写法:
1 select e.empno,e.ename,deptno,d.dname,d.loc from emp e join dept d
2* using (deptno)
SQL> /
EMPNO ENAME DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
7369 SMITH 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
7566 JONES 20 RESEARCH DALLAS
7654 MARTIN 30 SALES CHICAGO
using连接与natural连接中只要是两张表相同的字段,在select子句中都不能指定是哪张表。
这里跟left join,right join,等值连接不一样,它们要求指定具体的表名,以防止列名的含糊不清。
这两种连接都不能有限制词。也就是说不能去指定具体的哪个表中拥有该列。
这种写法也可以用等值连接。
3.等值连接
SQL> select empno,ename,d.deptno,d.dname from emp e join dept d
2 on e.deptno = d.deptno;
EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
4.左外连接:
SQL> select empno,ename,d.deptno,dname,loc from emp e left join
2 dept d on e.deptno = d.deptno;
EMPNO ENAME DEPTNO DNAME LOC
---------- ---------- ---------- -------------- -------------
7934 MILLER 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
7782 CLARK 10 ACCOUNTING NEW YORK
7902 FORD 20 RESEARCH DALLAS
5右外连接:
SQL> select ename,d.deptno,dname,loc from emp e right join dept d
2 on e.deptno = d.deptno;
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
SMITH 20 RESEARCH DALLAS
ALLEN 30 SALES CHICAGO
WARD 30 SALES CHICAGO
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
BLAKE 30 SALES CHICAGO
6.全外连接:
SQL> select ename,d.deptno,dname,loc from emp e full join dept d
2 on e.deptno = d.deptno;
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
MILLER 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
MARTIN 30 SALES CHICAGO
WARD 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
40 OPERATIONS BOSTON
如果要连接n张表,那么条件是n-1个条件。这里跟旧语法一样。
比如连接三张表,那么需要2个条件。
7.不等值连接
SQL> select ename,sal,grade from emp e join salgrade s
on e.sal between s.losal and s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
ADAMS 1100 1
JAMES 950 1
WARD 1250 2
8.自连接
SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER join EMP MANAGER
on WORKER.MGR = MANAGER.EMPNO
9.附加条件:and
SQL> select ename,dname from emp e join dept d
2 on e.deptno = d.deptno and e.deptno=10;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
在查询的使用,通常我们要对表名加别名,指定每个字段的
来自得表,这样能够提高查询效率。
旧语法
1.笛卡尔积
SQL> SELECT e.DEPTNO,DNAME,LOC,ENAME ,SAL FROM EMP e, DEPT d;
2.等值连接
SQL> select ename,dname from emp e, dept d
2 where e.deptno = d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
3.左外连接
SQL> select ename,dname from emp e , dept d
2 where e.deptno = d.deptno(+);
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
4.右外连接
SQL> select ename,dname from emp e , dept d
2 where e.deptno(+) = d.deptno;
5.不等值连接
1 select ename,sal,grade from emp e , salgrade s
2* where e.sal between s.losal and s.hisal
SQL> /
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
ADAMS 1100 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
6.自连接
SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24984814/viewspace-708003/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24984814/viewspace-708003/