sql中的联表操作

测试数据

SQL> desc persons
名称                                     是否为空? 类型
 ------------------------------------------------- ----------------------------
 ID_P                                              NUMBER(38)
 NAME                                              VARCHAR2(20)
 ADDRESS                                           VARCHAR2(20)
 
SQL> desc orders
 名称                                      是否为空? 类型
 ------------------------------------------------- ----------------------------
 ID_O                                              NUMBER(38)
 ORDERNO                                           NUMBER(38)
 ID_P                                               NUMBER(38)
SQL> select * from persons;
 
     ID_P NAME                                     ADDRESS
------------------------------------------------------------------------------------------
        1 ewww2006                                haidian
        2 yangxiao                                 xicheng
        3 nubnub                                   yanqing
 
SQL> select * from orders;
 
     ID_O    ORDERNO       ID_P
---------- ---------- ----------
        1       1001          1
        2       1002          1
        3       1003          2
        4       1004

1、笛卡儿积，这个是将第一个表的每一行和第二个表的每一行联接成一行生成的所有数据。如果不止两个表的话依此类推。如果只想选择其中的一部分数据的话，可以使用where子句。

SQL> select name, orderno
 2  from persons, orders;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
ewww2006                                       1003
ewww2006                                       1004
yangxiao                                       1001
yangxiao                                       1002
yangxiao                                       1003
yangxiao                                       1004
nubnub                                         1001
nubnub                                         1002
nubnub                                         1003
 
NAME                                        ORDERNO
--------------------------------------------------
nubnub                                         1004
 
已选择12行。
 
SQL> select name, orderno
 2  from persons, orders
 3  where persons.id_p =orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003

 

2、inner join on 内联接，数据库管理系统也识别join on ，二者相同。必须有关键词on，后跟条件，其含义和普通sql查询里的where是一样的。该语句返回满足条件的结果。

SQL> select name, orderno
 2  from persons inner join orders on persons.id_p = orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003

 

3、left join on  左外联接，和inner join on的不同就是，如果左边的那个表的数据行有不满足条件的行，inner join on不会返回这些行，但是left join on会。

SQL> select name, orderno
 2  from persons left join orders on persons.id_p = orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
nubnub

 

4、right join on 右外联接。如果右边的那个表的数据行有不满足条件的行，inner join on不会返回这些行，但是right join on会。

SQL> select name, orderno
 2  from persons right join orders on persons.id_p = orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1002
ewww2006                                       1001
yangxiao                                       1003
                                              1004

 

5、full join on 全外联接。可以认为是leftjoin on 和right join on 的结果的并。

SQL> select name, orderno
 2  from persons full join orders on persons.id_p = orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
                                               1004
Nubnub

 

6、如果两个表做等值联接，并且作为联接条件的字段名称和类型相同，可以使用using

 

SQL> select name, orderno
 2  from persons inner join orders using(id_p);
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
 
SQL> select name, orderno
 2  from persons left join orders using(id_p);
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
nubnub
 
SQL> select name, orderno
 2  from persons right join orders using(id_p);
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1002
ewww2006                                       1001
yangxiao                                      1003
                                              1004
 
SQL> select name, orderno
 2  from persons full join orders using(id_p);
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
                                              1004
nubnub
 
SQL>

 

7、natural join 自然联接。它会根据两个表里的共同属性建立等值联接。

SQL> select name, orderno
 2  from persons natural join orders;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
 
SQL>

 

8、cross join where  交叉联接(有时也称全联接)。注意，这里使用的是where而不是on，cross join和笛卡儿积作用相同

SQL> select name, orderno
 2  from persons cross join orders where persons.id_p = orders.id_p;
 
NAME                                        ORDERNO
--------------------------------------------------
ewww2006                                       1001
ewww2006                                       1002
yangxiao                                       1003
 
SQL>

发现一个有趣的现象，同时也是值得注意的问题：当使用联接操作时，可能会牵涉到隐式的类型转换

SQL>desc orders2;
 名称                          是否为空? 类型
 ----------------------------------------- ------------------------------------
 ID_O                                    NUMBER(38)
 ORDERNO                                 NUMBER(38)
 ID_P                                    VARCHAR2(10)


SQL> select name, orderno
  2  from persons inner join orders2 using(id_p);


NAME                                       ORDERNO
---------------------------------------- ----------
ewww2006                                      1001
ewww2006                                      1002
yangxiao                                      1003


SQL> select name, orderno
  2  from persons natural join orders2;


NAME                                       ORDERNO
---------------------------------------- ----------
ewww2006                                      1001
ewww2006                                      1002
yangxiao                                      1003


SQL>

可以看到，orders2.id_p是varchar2(10)型的，而persons.id_p的类型是number型。这里却可以进行联接。这种智能的做法可能会带来方便，但是也可能带来潜在的错误，除非你清楚地知道自己做的一切。