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