Oracle表连接小结

最新推荐文章于 2024-07-18 14:34:06 发布

只是甲

最新推荐文章于 2024-07-18 14:34:06 发布

阅读量1.1k

点赞数 3

文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/u010520724/article/details/105896282

版权

文章目录

备注:测试数据库版本为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 id2
from t1
inner join t2
     on t1.id = t2.id;
--或者
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id;

SQL> select t1.id,t2.id id2
  2  from t1
  3  inner join t2
  4       on t1.id = t2.id
  5  /
        ID        ID2
---------- ----------
         2          2

SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id = t2.id;
        ID        ID2
---------- ----------
         2          2

二.左连接 --求A的全部

select t1.id,t2.id id2
from t1
left join t2
  on t1.id = t2.id;
-- Oracle独有 (哪边数据少加号在哪边)
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id(+);

SQL> select t1.id,t2.id id2
  2  from t1
  3  left join t2
  4    on t1.id = t2.id;
        ID        ID2
---------- ----------
         2          2
         1 

SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id = t2.id(+);
        ID        ID2
---------- ----------
         2          2
         1

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

select t1.id,t2.id id2
from t1
left join t2
  on t1.id = t2.id
where t2.id is null;
--oracle
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id(+)
and   t2.id is null;

SQL> select t1.id,t2.id id2
  2  from t1
  3  left join t2
  4    on t1.id = t2.id
  5  where t2.id is null;
        ID        ID2
---------- ----------
         1 

SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id = t2.id(+)
  4  and   t2.id is null;
        ID        ID2
---------- ----------
         1

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

select t1.id,t2.id id2
from t1
full outer join t2
   on t1.id = t2.id;

select t1.id,t2.id id2
from t1
full outer join t2
   on t1.id = t2.id;

--oracle +无法实现全连接,得再使用一次union
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id(+)
union 
select t1.id,t2.id id2
from t1,t2
where t1.id(+) = t2.id;

SQL> select t1.id,t2.id id2
  2  from t1
  3  full outer join t2
  4     on t1.id = t2.id;
        ID        ID2
---------- ----------
         2          2
                    3
         1 

SQL> 
SQL> 
SQL> select t1.id,t2.id id2
  2  from t1
  3  full outer join t2
  4     on t1.id = t2.id;
        ID        ID2
---------- ----------
         2          2
                    3
         1 

SQL> 
SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id = t2.id(+)
  4  union
  5  select t1.id,t2.id id2
  6  from t1,t2
  7  where t1.id(+) = t2.id;
        ID        ID2
---------- ----------
         1 
         2          2
                    3

五.表连接实现-去交集

select t1.id,t2.id id2
from t1
full outer join t2
   on t1.id = t2.id
where t1.id is null
or   t2.id is null;

--Oracle
select t1.id,t2.id id2
from t1,t2
where t1.id = t2.id(+)
and   t2.id is null
union
select t1.id,t2.id id2
from t1,t2
where t1.id(+) = t2.id
and   t1.id is null;

SQL> select t1.id,t2.id id2
  2  from t1
  3  full outer join t2
  4     on t1.id = t2.id
  5  where t1.id is null
  6  or   t2.id is null;
        ID        ID2
---------- ----------
                    3
         1 

SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id = t2.id(+)
  4  and   t2.id is null
  5  union
  6  select t1.id,t2.id id2
  7  from t1,t2
  8  where t1.id(+) = t2.id
  9  and   t1.id is null
 10  /
        ID        ID2
---------- ----------
         1 
                    3

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

select t1.id,t2.id id2
from t1
right join t2
  on t1.id = t2.id
where t1.id is null;
--Oracle
select t1.id,t2.id id2
from t1,t2
where t1.id(+) = t2.id
and   t1.id is null;

SQL> select t1.id,t2.id id2
  2  from t1
  3  right join t2
  4    on t1.id = t2.id
  5  where t1.id is null;
        ID        ID2
---------- ----------
                    3

SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id(+) = t2.id
  4  and   t1.id is null;
        ID        ID2
---------- ----------
                    3

七.右连接 --求B的全部

select t1.id,t2.id id2
from t1
right join t2
 on t1.id = t2.id;

--Oracle
select t1.id,t2.id id2
from t1,t2
where t1.id(+) = t2.id;

SQL> select t1.id,t2.id id2
  2  from t1
  3  right join t2
  4   on t1.id = t2.id;
        ID        ID2
---------- ----------
         2          2
                    3

SQL> 
SQL> select t1.id,t2.id id2
  2  from t1,t2
  3  where t1.id(+) = t2.id;
        ID        ID2
---------- ----------
         2          2
                    3

八.表的笛卡尔积

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

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

测试数据:

create table test1(c  varchar2(200));
insert into test1 values('中国');
insert into test1 values('美国');
insert into test1 values('日本');
insert into test1 values('韩国');
commit;

现在有4个国家要进行比赛，每个国家都要和除了自己之外的另外3个国家进行比赛，这个sql如何写?

--通过表连接构造一个笛卡尔积
select t1.c c1,t2.c c2
from test1 t1,test1 t2
where 1 = 1
order by t1.c,t2.c;
--去掉自己和自己的
select t1.c c1,t2.c c2
from test1 t1,test1 t2
where t1.c != t2.c
order by t1.c,t2.c;

SQL> select t1.c c1,t2.c c2
  2  from test1 t1,test1 t2
  3  where 1 = 1
  4  order by t1.c,t2.c;
C1                                                                               C2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
韩国                                                                             韩国
韩国                                                                             美国
韩国                                                                             日本
韩国                                                                             中国
美国                                                                             韩国
美国                                                                             美国
美国                                                                             日本
美国                                                                             中国
日本                                                                             韩国
日本                                                                             美国
日本                                                                             日本
日本                                                                             中国
中国                                                                             韩国
中国                                                                             美国
中国                                                                             日本
中国                                                                             中国
16 rows selected

SQL> 
SQL> select t1.c c1,t2.c c2
  2  from test1 t1,test1 t2
  3  where t1.c != t2.c
  4  order by t1.c,t2.c;
C1                                                                               C2
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
韩国                                                                             美国
韩国                                                                             日本
韩国                                                                             中国
美国                                                                             韩国
美国                                                                             日本
美国                                                                             中国
日本                                                                             韩国
日本                                                                             美国
日本                                                                             中国
中国                                                                             韩国
中国                                                                             美国
中国                                                                             日本
12 rows selected