ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 两表连接语法

两表连接语法

原创 Linux操作系统 作者:wengjie_0627 时间:2011-09-21 11:02:11 0 删除 编辑

新语法
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: join方法介绍
请登录后发表评论 登录
全部评论

注册时间:2011-05-19

  • 博文量
    50
  • 访问量
    27791