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

进开发学习交流群：