ORACLE 表间的联结方式

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;