ANSI sql join

http://psoug.org/

Cross Join 又成为笛卡尔连接

CREATE TABLE table_one (
  col_one NUMBER,
  col_two VARCHAR2(10)
);
 
CREATE TABLE table_two (
  col_three NUMBER,
  col_four  VARCHAR2(10)
);
 
INSERT INTO table_one VALUES ( 1,    'one');
INSERT INTO table_one VALUES ( 2,    'two');
 
INSERT INTO table_two VALUES (10,    'ten');
INSERT INTO table_two VALUES (20, 'twenty');
INSERT INTO table_two VALUES ( 5,   'five');
 
SELECT * FROM
  table_one cross join
  table_two;
 
 
-- Each row from table_one is returned together 
-- with each row from table_two:
 
   COL_ONE COL_TWO     COL_THREE COL_FOUR
---------- ---------- ---------- ----------
         1 one                10 ten
         1 one                20 twenty
         1 one                 5 five
         2 two                10 ten
         2 two                20 twenty
         2 two                 5 five
 

Full Join
CREATE TABLE left_tbl (
  id  NUMBER,
  txt VARCHAR2(10)
);
 
CREATE TABLE right_tbl (
  id  NUMBER,
  txt VARCHAR2(10)
);
 
  INSERT INTO  left_tbl VALUES (1, 'one'   );
  INSERT INTO  left_tbl VALUES (2, 'two'   );
  INSERT INTO  left_tbl VALUES (3, 'three' );
--insert into  left_tbl values (4, 'four'  );
  INSERT INTO  left_tbl VALUES (5, 'five'  );
 
  INSERT INTO right_tbl VALUES (1, 'uno'   );
--insert into right_tbl values (2, 'dos'   );
  INSERT INTO right_tbl VALUES (3, 'tres'  );
  INSERT INTO right_tbl VALUES (4, 'cuatro');
  INSERT INTO right_tbl VALUES (5, 'cinco' );
 
 
-- A full join returns the records of both tables 
-- (that satisfy a [potential] where condition). In 
-- the following example, 4 cuatro and 2 two are returned, 
-- although the ids 4 and 2 are not present in both tables:
 
SELECT
             id,
           l.txt,
           r.txt
  FROM
            left_tbl l full join
           right_tbl r using(id)
          id;
 
        ID TXT        TXT
---------- ---------- ----------
         1 one        uno
         2 two
         3 three      tres
         4            cuatro
         5 five       cinco

Inner Join
CREATE TABLE table_one (
  col_one NUMBER,
  col_two NUMBER
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two NUMBER
);
 
INSERT INTO table_one VALUES ( 1, 1);
INSERT INTO table_one VALUES ( 3, 5);
INSERT INTO table_one VALUES ( 5, 9);
 
INSERT INTO table_two VALUES ( 4, 5);
INSERT INTO table_two VALUES ( 6, 3);
INSERT INTO table_two VALUES ( 5, 5);
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 ON t1.col_one = t2.col_two;
 
   COL_ONE    COL_TWO    COL_ONE    COL_TWO
---------- ---------- ---------- ----------
         5          9          4          5
         3          5          6          3
         5          9          5          5
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 using (col_two);
 
-- Note: col_two is only returned once here instead of twice
-- when using is used instead of on. This is because it must 
-- be the same value:
 
   COL_TWO    COL_ONE    COL_ONE
---------- ---------- ----------
         5          3          4
         5          3          5

Outer Join
CREATE TABLE table_one (
  col_one NUMBER,
  col_two CHAR(1)
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two CHAR(1)
);
 
INSERT INTO table_one VALUES (1, 'a');
INSERT INTO table_one VALUES (2, 'b');
INSERT INTO table_one VALUES (3, 'c');
 
INSERT INTO table_two VALUES (2, 'B');
INSERT INTO table_two VALUES (3, 'C');
INSERT INTO table_two VALUES (4, 'D');
 
SELECT * FROM 
  table_one t1 left outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
 
SELECT * FROM 
  table_one t1 right outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
                      4 D
 
SELECT * FROM 
  table_one t1 full outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
                      4 D