Oracle笔记十六：多表连接/并差交集

1、多表连接

注意：
当两表关联条件不唯一时，容易出现笛卡尔积

1.1、内连接（Inner Join/Join）

with tmp1 as (
			SELECT 1 AS ID , 10 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 2 AS ID , 10 AS DEPT , '20200123' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 3 AS ID , 20 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 4 AS ID , 30 AS DEPT , '20200301' AS VALUE_DT FROM DUAL 
			)
,TMP2 AS (
			SELECT 10 AS DEPT , 'LLL' AS DEPT_NAME FROM DUAL UNION ALL
			SELECT 20 AS DEPT , 'MMM' AS DEPT_NAME FROM DUAL UNION ALL
			SELECT 50 AS DEPT , 'AAA' AS DEPT_NAME FROM DUAL	
	     )
SELECT * FROM TMP1 A
INNER JOIN TMP2 B
ON A.DEPT = B.DEPT
;

结果如下：
  	ID	DEPT	VALUE_DT	DEPT	DEPT_NAME
	1	10		20200101	10		LLL
	2	10		20200123	10		LLL
	3	20		20200101	20		MMM

	
with tmp1 as (
			SELECT 1 AS ID , 10 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 2 AS ID , 10 AS DEPT , '20200123' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 3 AS ID , 20 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 4 AS ID , 30 AS DEPT , '20200301' AS VALUE_DT FROM DUAL 
			)
,TMP2 AS (
			SELECT 10 AS DEPT , 'LLL' AS DEPT_NAME FROM DUAL UNION ALL
			SELECT 20 AS DEPT , 'MMM' AS DEPT_NAME FROM DUAL UNION ALL
			SELECT 50 AS DEPT , 'AAA' AS DEPT_NAME FROM DUAL	
	     )
SELECT * FROM TMP1 A
INNER JOIN TMP2 B
ON A.DEPT = B.DEPT
;

结果如下：
  	ID	DEPT	VALUE_DT	DEPT	DEPT_NAME
	1	10		20200101	10		LLL
	2	10		20200123	10		LLL
	3	20		20200101	20		MMM

with tmp1 as (
			SELECT 1 AS ID , 10 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 2 AS ID , 10 AS DEPT , '20200123' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 3 AS ID , 20 AS DEPT , '20200101' AS VALUE_DT FROM DUAL UNION ALL
			SELECT 4