ORACLE 表间的联结方式

内联结左联结右联结自然联结全联结笛卡尔联结

创建测试数据

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;