Oracle表连接小结

文章目录

一.内连接 –求交集
二.左连接 –求A的全部
三.表连接 –实现A-B的差集
四.全连接 — A union B 求合集
五.表连接实现-去交集
六.表连接实现-B-A 求差集
七.右连接 –求B的全部
八.表的笛卡尔积

备注:测试数据库版本为Oracle 11g R2

这个blog我们来聊聊常见的表连接的方法

测试数据:

create table t1(id number);create table t2(id number);insert into t1 values(1);insert into t1 values(2);insert into t2 values(2);insert into t2 values(3);commit;

一.内连接 –求交集

select t1.id,t2.id id2from t1inner join t2on t1.id = t2.id;--或者select t1.id,t2.id id2from t1,t2where t1.id = t2.id;

SQL> select t1.id,t2.id id22  from t13  inner join t24       on t1.id = t2.id5  /ID        ID2---------- ----------2          2SQL> select t1.id,t2.id id22  from t1,t23  where t1.id = t2.id;ID        ID2---------- ----------2          2

二.左连接 –求A的全部

select t1.id,t2.id id2from t1left join t2on t1.id = t2.id;-- Oracle独有 (哪边数据少加号在哪边)select t1.id,t2.id id2from t1,t2where t1.id = t2.id(+);

SQL> select t1.id,t2.id id22  from t13  left join t24    on t1.id = t2.id;ID        ID2---------- ----------2          21SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id = t2.id(+);ID        ID2---------- ----------2          21

三.表连接 –实现A-B的差集

select t1.id,t2.id id2from t1left join t2on t1.id = t2.idwhere t2.id is null;--oracleselect t1.id,t2.id id2from t1,t2where t1.id = t2.id(+)and   t2.id is null;

SQL> select t1.id,t2.id id22  from t13  left join t24    on t1.id = t2.id5  where t2.id is null;ID        ID2---------- ----------1SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id = t2.id(+)4  and   t2.id is null;ID        ID2---------- ----------1

四.全连接 – A union B 求合集

select t1.id,t2.id id2from t1full outer join t2on t1.id = t2.id;select t1.id,t2.id id2from t1full outer join t2on t1.id = t2.id;--oracle +无法实现全连接,得再使用一次unionselect t1.id,t2.id id2from t1,t2where t1.id = t2.id(+)unionselect t1.id,t2.id id2from t1,t2where t1.id(+) = t2.id;

SQL> select t1.id,t2.id id22  from t13  full outer join t24     on t1.id = t2.id;ID        ID2---------- ----------2          231SQL>SQL>SQL> select t1.id,t2.id id22  from t13  full outer join t24     on t1.id = t2.id;ID        ID2---------- ----------2          231SQL>SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id = t2.id(+)4  union5  select t1.id,t2.id id26  from t1,t27  where t1.id(+) = t2.id;ID        ID2---------- ----------12          23

五.表连接实现-去交集

select t1.id,t2.id id2from t1full outer join t2on t1.id = t2.idwhere t1.id is nullor   t2.id is null;--Oracleselect t1.id,t2.id id2from t1,t2where t1.id = t2.id(+)and   t2.id is nullunionselect t1.id,t2.id id2from t1,t2where t1.id(+) = t2.idand   t1.id is null;

SQL> select t1.id,t2.id id22  from t13  full outer join t24     on t1.id = t2.id5  where t1.id is null6  or   t2.id is null;ID        ID2---------- ----------31SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id = t2.id(+)4  and   t2.id is null5  union6  select t1.id,t2.id id27  from t1,t28  where t1.id(+) = t2.id9  and   t1.id is null10  /ID        ID2---------- ----------13

六.表连接实现-B-A 求差集

select t1.id,t2.id id2from t1right join t2on t1.id = t2.idwhere t1.id is null;--Oracleselect t1.id,t2.id id2from t1,t2where t1.id(+) = t2.idand   t1.id is null;

SQL> select t1.id,t2.id id22  from t13  right join t24    on t1.id = t2.id5  where t1.id is null;ID        ID2---------- ----------3SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id(+) = t2.id4  and   t1.id is null;ID        ID2---------- ----------3

七.右连接 –求B的全部

select t1.id,t2.id id2from t1right join t2on t1.id = t2.id;--Oracleselect t1.id,t2.id id2from t1,t2where t1.id(+) = t2.id;

SQL> select t1.id,t2.id id22  from t13  right join t24   on t1.id = t2.id;ID        ID2---------- ----------2          23SQL>SQL> select t1.id,t2.id id22  from t1,t23  where t1.id(+) = t2.id;ID        ID2---------- ----------2          23

八.表的笛卡尔积

如果表连接没有带条件，则会产生笛卡尔积
假设A表和B表都是10条记录，且一一对应，这个时候A、B两个表无关联条件下的查询，会产生10*10 100条数据。
笛卡尔积在开发中，大多数时候都是不可取的，严重的影响性能，我就优化过不写表关联条件再来distinct去重的开发写的sql。

今天我们来讲讲一个笛卡尔积的例子。