oracle的cross join,cross join求缺少的流程

-- 原表

SQL> select * from DATAFLOWDIAGRAMS;

DIAGRAM_NA BUBBLE_NAM FLOW_NAME

---------- ---------- ----------

Proc1 crunch facts

Proc1 crunch guesses

Proc1 crunch opinions

Proc1 input guesses

Proc1 input opinions

Proc1 output facts

Proc1 output guesses

Proc2 reckon guesses

Proc2 reckon opinions

9 rows selected.

-- 要求出缺少的流程

-- 通过cross join 自连接求出所有的程序名SQL> SELECT F1.DIAGRAM_NAME, F1.BUBBLE_NAME, F2.FLOW_NAME

2 FROM (SELECT DISTINCT F1.DIAGRAM_NAME, F1.BUBBLE_NAME

3 FROM DATAFLOWDIAGRAMS F1) F1

4 CROSS JOIN (SELECT DISTINCT F2.FLOW_NAME FROM DATAFLOWDIAGRAMS F2) F2;

DIAGRAM_NA BUBBLE_NAM FLOW_NAME

---------- ---------- ----------

Proc2 reckon opinions

Proc2 reckon facts

Proc2 reckon guesses

Proc1 crunch opinions

Proc1 crunch facts

Proc1 crunch guesses

Proc1 output opinions

Proc1 output facts

Proc1 output guesses

Proc1 input opinions

Proc1 input facts

Proc1 input guesses

-- 最后结果

SQL> with x as

2 (SELECT F1.DIAGRAM_NAME, F1.BUBBLE_NAME, F2.FLOW_NAME

3 FROM (SELECT DISTINCT F1.DIAGRAM_NAME, F1.BUBBLE_NAME

4 FROM DATAFLOWDIAGRAMS F1) F1

5 CROSS JOIN (SELECT DISTINCT F2.FLOW_NAME FROM DATAFLOWDIAGRAMS F2) F2)

6 select x.*

7 from x

8 left join DATAFLOWDIAGRAMS t

9 on t.diagram_name = x.diagram_name

10 and t.bubble_name = x.bubble_name

11 and t.flow_name = x.flow_name

12 where t.diagram_name is null

13 /

DIAGRAM_NA BUBBLE_NAM FLOW_NAME

---------- ---------- ----------

Proc2 reckon facts

Proc1 output opinions

Proc1 input facts