oracle怎样关联,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