ORACLE关联

1、创建测试数据：

CREATE TABLE TBL_TEST1(F_ID NUMBER(10) PRIMARY KEY);
CREATE TABLE TBL_TEST2(D_ID NUMBER(10) PRIMARY KEY, F_ID NUMBER(10));

BEGIN
  FOR I IN 1 .. 3 LOOP
    INSERT INTO TBL_TEST1 VALUES (I);
    INSERT INTO TBL_TEST2 VALUES (I, I);
  END LOOP;
  FOR I IN 4 .. 5 LOOP
    INSERT INTO TBL_TEST1 VALUES (I);
  END LOOP;
  FOR I IN 4 .. 5 LOOP
    INSERT INTO TBL_TEST2 VALUES (I, 0);
  END LOOP;
  COMMIT;
END;
/

 

生成的数据如下：

SQL> SELECT * FROM TBL_TEST1;
 
       F_ID
-----------
          1
          2
          3
          4
          5
SQL> SELECT * FROM TBL_TEST2;
 
       D_ID        F_ID
----------- -----------
          1           1
          2           2
          3           3
          4           0
          5           0

 

2、内连接：

-- 内连接;
SELECT * FROM TBL_TEST1 T1, TBL_TEST2 T2 WHERE T1.F_ID = T2.F_ID;

 

得到的结果如下：

       F_ID        D_ID        F_ID
----------- ----------- -----------
          1           1           1
          2           2           2
          3           3           3

 

3、右外关联：

-- T1右外关联T2;
SELECT * FROM TBL_TEST1 T1, TBL_TEST2 T2 WHERE T1.F_ID(+) = T2.F_ID;
SELECT *
  FROM TBL_TEST1 T1
 RIGHT OUTER JOIN TBL_TEST2 T2 ON (T1.F_ID = T2.F_ID);

 

上面两个SQL是等价的，执行结果如下：

       F_ID        D_ID        F_ID
----------- ----------- -----------
          1           1           1
          2           2           2
          3           3           3
                      4           0
                      5           0

 

4、左外关联：

-- T1左外关联T2;
SELECT * FROM TBL_TEST1 T1, TBL_TEST2 T2 WHERE T1.F_ID = T2.F_ID(+);
SELECT *
  FROM TBL_TEST1 T1
  LEFT OUTER JOIN TBL_TEST2 T2 ON (T1.F_ID = T2.F_ID);

 

执行结果如下：

       F_ID        D_ID        F_ID
----------- ----------- -----------
          1           1           1
          2           2           2
          3           3           3
          5             
          4             

 

5、全外关联：

-- 全外关联;
SELECT *
  FROM TBL_TEST1 T1
  FULL OUTER JOIN TBL_TEST2 T2 ON (T1.F_ID = T2.F_ID);

 

执行结果如下：

      F_ID        D_ID        F_ID
---------- ----------- -----------
         1           1           1
         2           2           2
         3           3           3
         5             
         4             
                     4           0
                     5           0