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

技術的發展日新月異，随着時間推移，無法保證本部落格所有内容的正确性。如有誤導，請大家見諒，歡迎評論區指正！ 個人開源代碼連結: GitHub:

https://github.com/ITMingliang

Gitee:

https://gitee.com/mingliang_it

GitLab:

https://gitlab.com/ITMingliang

進開發學習交流群：