full join ，left join ，right join，inner join

一、full  join 

----- full join
SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A
  FULL JOIN (SELECT 4 AS T1
               FROM DUAL
             UNION
             SELECT 3 AS T1 FROM DUAL) B
    ON A.T1 = B.T1

 

二、left  join 

---- left join 

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A
  LEFT JOIN (SELECT 4 AS T1
               FROM DUAL
             UNION
             SELECT 3 AS T1 FROM DUAL) B
    ON A.T1 = B.T1

----相当于

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A,
       (SELECT 4 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) B
 WHERE A.T1 = B.T1(+)

 

三、rightjoin 

---- right join 

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A
  RIGHT JOIN (SELECT 4 AS T1
               FROM DUAL
             UNION
             SELECT 3 AS T1 FROM DUAL) B
    ON A.T1 = B.T1

----相当于

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A,
       (SELECT 4 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) B
 WHERE A.T1(+) = B.T1

 

四、inner  join 

---- inner join 

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A
  INNER JOIN (SELECT 4 AS T1
               FROM DUAL
             UNION
             SELECT 3 AS T1 FROM DUAL) B
    ON A.T1 = B.T1

----相当于

SELECT A.*, B.*
  FROM (SELECT 0 AS T1
          FROM DUAL
        UNION
        SELECT 1 AS T1
          FROM DUAL
        UNION
        SELECT 2 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) A,
       (SELECT 4 AS T1
          FROM DUAL
        UNION
        SELECT 3 AS T1 FROM DUAL) B
 WHERE A.T1 = B.T1

 

内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/wml-it/p/13650610.html

标签： oracle数据库 orcale开发 oracle技术文章
你还没有登录，请先登录或注册！
  • 还没有人评论，欢迎说说您的想法！