ORACLE 表间的联结方式

1. 内联结
2. 左联结
3. 右联结
4. 自然联结
5. 全联结
6. 笛卡尔联结

创建测试数据

CREATE TABLE TMP_TB_1(USER_ID   NUMBER(10,0),USER_NAME VARCHAR2(30),ADDRESS   VARCHAR2(50));CREATE TABLE TMP_TB_2(USER_ID   NUMBER(10,0),USER_NAME VARCHAR2(30),U_ADDRESS   VARCHAR2(50));-- 插入数据INSERT INTO TMP_TB_1 VALUES (1,'小明','李家胡同');INSERT INTO TMP_TB_1 VALUES (2,'李君','李家胡同');INSERT INTO TMP_TB_1 VALUES (3,'小黑','五里坨');INSERT INTO TMP_TB_1 VALUES (4,'提提','南宫');INSERT INTO TMP_TB_1 VALUES (7,'酷酷','大红门');INSERT INTO TMP_TB_1 VALUES (8,'李老师','天安门');SELECT * FROM TMP_TB_1;-- 插入数据INSERT INTO TMP_TB_2 VALUES (1,'小明','李家胡同');INSERT INTO TMP_TB_2 VALUES (2,'李君','李家胡同');INSERT INTO TMP_TB_2 VALUES (3,'小宝宝','卡瓦挖');INSERT INTO TMP_TB_2 VALUES (5,'多多','国贸');INSERT INTO TMP_TB_2 VALUES (6,'荣荣','上京');SELECT * FROM TMP_TB_2;

1 内联结

SELECT A.*,B.*  FROM TMP_TB_1 A, TMP_TB_2 B  WHERE A.USER_ID = B.USER_ID;SELECT A.*,B.*  FROM TMP_TB_1 A JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID;SELECT A.*,B.*  FROM TMP_TB_1 A INNER JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID;

2 左外联接

SELECT A.*,B.*  FROM TMP_TB_1 A, TMP_TB_2 B  WHERE A.USER_ID = B.USER_ID(+);SELECT A.*,B.*  FROM TMP_TB_1 A   LEFT JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID;SELECT A.*,B.*  FROM TMP_TB_1 A   LEFT OUTER JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID;

带筛选条件的的左(外)联接

-- 先筛选后联接SELECT A.*,B.*  FROM TMP_TB_1 A, TMP_TB_2 B  WHERE A.USER_ID = B.USER_ID(+)    AND B.U_ADDRESS(+) = '李家胡同';SELECT A.*,B.*  FROM TMP_TB_1 A   LEFT JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID    AND B.U_ADDRESS = '李家胡同';-- 左外联接 联接后根据条件筛选SELECT A.*,B.*  FROM TMP_TB_1 A, TMP_TB_2 B  WHERE A.USER_ID = B.USER_ID(+)    AND B.U_ADDRESS = '李家胡同';SELECT A.*,B.*  FROM TMP_TB_1 A   LEFT JOIN TMP_TB_2 B    ON A.USER_ID = B.USER_ID WHERE B.U_ADDRESS = '李家胡同';

3 右(外)联接 同左(外)联接

4 自然连接

 SELECT *  FROM TMP_TB_1 A   NATURAL JOIN TMP_TB_2 B;

5 全(外)联接

 SELECT *  FROM TMP_TB_1 A   FULL JOIN TMP_TB_2 B   ON A.USER_ID = B.USER_ID AND A.USER_NAME = B.USER_NAME;  SELECT *  FROM TMP_TB_1 A   FULL OUTER JOIN TMP_TB_2 B   ON A.USER_ID = B.USER_ID; SELECT *  FROM TMP_TB_1 A   FULL JOIN TMP_TB_2 B   ON A.USER_ID = B.USER_ID AND A.USER_NAME = B.USER_NAME;

6 笛卡尔积

SELECT *  FROM TMP_TB_1 A, TMP_TB_2 B; SELECT *  FROM TMP_TB_1 A   CROSS JOIN TMP_TB_2 B;