full join ,left join ,right join,inner join

Time:2021-4-19

full join ,left join ,right join,inner join

1、 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

 

2、 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

----Equivalent to

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(+)

 

3、 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

----Equivalent to

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

 

4、 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

----Equivalent to

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

 

Recommended Today

Installation and configuration of node.js and NPM

Installation of node.js CentOS installation: $ dnf module list nodejs $ dnf module install nodejs:14 Windows installation: https://nodejs.org/en/download/ To view the installed version: $ node -v NPM installation Node.js has a built-in NPM package management tool, which does not need to be installed separately. To view the installed version: $ npm -v Upgrade NPM to […]