oracle sql 内连接 左外连接 右外连接 全外连接

1.创建测试表并准备测试数据
sec@ora10g> create table a (a number(1),b number(1),c number(1));
sec@ora10g> create table b (a number(1),d number(1),e number(1));
sec@ora10g> insert into a values(1,1,1);
sec@ora10g> insert into a values(2,2,2);
sec@ora10g> insert into a values(3,3,3);
sec@ora10g> insert into b values(1,4,4);
sec@ora10g> insert into b values(2,5,5);
sec@ora10g> insert into b values(4,6,6);
sec@ora10g> commit;

sec@ora10g> select * from a;
       
A          B          C
---------- ---------- ----------
        
1          1          1
        
2          2          2
        
3          3          3


sec@ora10g> select * from b;

        
A          D          E
---------- ---------- ----------
        
1          4          4
        
2          5          5
        
4          6          6

2. 内连接
sec@ora10g> select * from a, b where a.a=b.a;
另外一种写法如下
sec@ora10g> select * from  a inner join b on a.a=b.a;

        
A        B          C          A          D          E

-------- ---------- ---------- ---------- ---------- ----------
        
1        1          1          1          4          4
        
2        2          2          2          5          5

3.左外连接
sec@ora10g> select * from  a,b where a.a=b.a(+);
另外一种写法如下
sec@ora10g> select *  from  a  left outer join b on a.a=b.a;

        
A          B          C          A          D          E
---------- ---------- ---------- ---------- ---------- ----------
        
1          1          1          1          4          4
        
2          2          2          2          5          5
        
3          3          3

4.右外连接
sec@ora10g> select * from  a,b where a.a(+)=b.a;
另外一种写法如下
sec@ora10g> select * from  a  right outer join b on a.a=b.a;

        
A          B          C          A          D          E
---------- ---------- ---------- ---------- ---------- ----------
        
1          1          1          1          4          4
        
2          2          2          2          5          5
                                         
　　　　　　　　　　　　　　　　　　　　4          6          6
5.全外连接
sec@ora10g> select * from  a full outer join b on a.a=b.a;

        
A          B          C          A          D          E
---------- ---------- ---------- ---------- ---------- ----------
        
1          1          1          1          4          4
        
2          2          2          2          5          5
        
3          3          3
                                         
　　　　　　　　　　　　　　　　　　　　4          6          6

转载于:https://www.cnblogs.com/pumushan/p/4661844.html