Oracle 表关联、半关联、反关联

一、 表关联

先建两个测试表

create table t1(id int,name varchar2(10));
create table t2(id int,name varchar2(10));

SQL> select * from t1;

        ID NAME
---------- ----------
         1 A
         2 b
         3 p
         4 L
           V

SQL> select * from t2;

        ID NAME
---------- ----------
         1 A
         2 b
         3 w
           c
         8 d

1. 各种表关联及返回结果

• inner join

SQL> select t1.*,t2.* from t1 join t2 on t1.id=t2.id;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w

• left join，以左表为主表

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id;  

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
         4 L
           V

• right join，以右表为主表

SQL> select t1.*,t2.* from t1 right join t2 on t1.id=t2.id;   

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
                               8 d
                                 c

• outer join

SQL> select t1.*,t2.* from t1 full join t2 on t1.id=t2.id;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
                                 c
                               8 d
         4 L
           V

2. 关联条件on 与 过滤条件where

       由于on的执行顺序优先于where，外关联写条件时必须注意是关联条件还是过滤条件，否则主表数据很可能会被where条件过滤掉。下面均以left join为例：

• 无额外条件时

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id;  

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
         4 L
           V

• 对left join右表加where条件

       对left join右表加where条件，会导致查询退化成inner join，因为左表可能无法返回所有数据。

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t2.id in (2,3);

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 b                   2 b
         3 p                   3 w
         
        由于where t2.id in (2,3)后执行，它过滤掉了t1表的3条数据，t1表的数据不完整，这样的结果不是正宗的left join。当然，有时就是需要这样的效果，不要太死板。

• 对left join右表加on条件

        正宗的left join（左表返回所有数据），右表条件应该加在on后面          

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t2.id in (2,3);

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 b                   2 b
         3 p                   3 w
         1 A
         4 L
           V
           
       t2表中只有id=2,3的数据会与t1表关联，其余为空。可以看到关联后，t1表还是完整的5行数据，t2表部分只有id=2,3，其余为空。

• 对left join左表加where条件

       同理，想要正宗的left join，主表的条件也不能加在where后面，否则主表会少数据。

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t1.name='b';

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 b                   2 b

• 对left join左表加on条件

SQL> select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t1.name='b';

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 b                   2 b
         1 A
         3 p
         4 L
           V

        本质上跟前面右表的on条件是类似的，t1.name='b' 查到 t1.id=2的数据，右表只有符合id=2的数据会与主表关联，其余为空，主表依然返回所有数据。

二、 半连接 in与exists

        in,exists 绝大部分情况下Oracle优化器会做语句改写，改写后是一样的（可以直接对比执行计划），所以效率是一样的。
除了在有connect by的时候，oracle对in的改写优于exists，建议用in。

        “半”连接是指子查询部分会去重，不需要都参与关联，有一条匹配就返回了。

SQL> select * from t1;

        ID NAME
---------- ----------
         1 A
         2 b
         3 p
         4 L
           V

-- 为了体现去重，我们给t2表搞一些重复数据

SQL> select * from t2 order by 1,2;

        ID NAME
---------- ----------
         1 A
         2 b
         2 b
         2 c
         3 c
         3 w
         8 d
           c

1. in与exists写法

SQL> select * from t1 where id in (select id from t2 where t2.name='b');  

        ID NAME
---------- ----------
         2 b 
         
SQL> select * from t1 where exists (select 1 from t2 where t1.id=t2.id and t2.name='b'); 

        ID NAME
---------- ----------
         2 b    

2. 改写

• 如果是单纯的in和exists，主表没有or条件，用inner join即可；如果有，要使用left join         
• 半连接会自动对子查询部分去重，改写后子查询部分依然要加distinct，否则是不等价的

SQL> select * from t1,(select distinct id from t2 where t2.name='b') v_t2 where t1.id=v_t2.id;

        ID NAME         ID
---------- ---------- ----------
         2 b                   2
         
       如果没有distinct，数据会有重复，因为两个t2.id=2的行都会被关联。当然，如果id字段本身就是唯一的，就可以用这种简单的写法。

SQL> select * from t1,t2 where t1.id=t2.id and t2.name='b';

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 b                   2 b
         2 b                   2 b  
         
       另外也注意distinct的位置，是对子查询去重而不是对整个返回结果去重，否则也是不等价的。

三、 反连接 not in与not exists

       not exists是exists的反向，但not in不是in的反向。not in效率低，并且稍不注意返回的结果会是错的，建议都用not exists。

      用回前面的t1,t2表，注意到两个表id都有null值。

SQL> select * from t1;

        ID NAME
---------- ----------
         1 A
         2 b
         3 p
         4 L
           V

SQL> select * from t2 order by 1,2;

        ID NAME
---------- ----------
         1 A
         2 b
         2 b
         2 c
         3 c
         3 w
         8 d
           c

1. 当子查询包含null值

       not in最典型的一个问题：子查询中包含一条null值时，整个查询返回null，绝大多数情况下这不是我们想要的。

SQL> select * from t1 where id not in (select id from t2);
no rows selected

它的原理是什么？

        t1.id not in (select t2.id from t2) 相当于 (t1.id <> 1 and ... and t1.id <> 8 and t1.id <> null)

        我们知道任何值都不能与null比较，所以t1.id <> null一定返回假。而and之中只要有一个是假，整个条件就为假，于是整个查询没有一条匹配值，只能返回空。

        对于 not exists，忽略子查询是否为空，因为它不是and的关系，只要匹配就可以返回。之前说过null<>null，所以t1的null值也匹配不到t2的，会返回。

SQL> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);

        ID NAME
---------- ----------
         4 L
           V

2. 改写

• 首先，需要改为left join，因为是以左表作为主表的

SQL> select * from t1 left join t2 on t1.id=t2.id;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
         2 b                   2 c
         2 b                   2 b
         3 p                   3 c
         4 L
           V
           
如果用inner join，t1表中id=4和null的值就没了，而这两个值实际是我们需要的。

SQL> select * from t1 join t2 on t1.id=t2.id;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 A                   1 A
         2 b                   2 b
         3 p                   3 w
         2 b                   2 c
         2 b                   2 b
         3 p                   3 c

• 其次，要找的是t1中有t2中没有的数据，即 left join后t2表没有匹配上的数据，即 t2.id is null

SQL> select * from t1 left join t2 on t1.id=t2.id where t2.id is null;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         4 L
           V
           
       我们前面说过in和exists改为join时子查询t2表部分要加distinct，这里需不需要？

       不需要，因为我们最后要的是匹配不上的（t2.id is null）的数据，is null没什么好去重的。

       加上去重结果是一致的，但除了拖慢性能没什么用。

SQL> select * from t1 left join (select distinct id from t2) v_t2 on t1.id=v_t2.id where v_t2.id is null;

        ID NAME               ID
---------- ---------- ----------
         4 L
           V