大家：
好！ 最近看了oracle连接中的on(1=1)的问题，挺有意思的。
测试表如下所示:
create table t1(c1 varchar2(10));
insert into t1 values('1');
insert into t1 values('2');
create table t2(c2 varchar2(10));
insert into t2 values('1');
简简单单的一个表，最多两条数据, 问下面的查询会出什么结果？
SELECT T1.C1, T2.C2, T3.C3
FROM T1
JOIN T2
ON (1 = 1)
LEFT JOIN (SELECT 1 AS C3 FROM DUAL UNION ALL SELECT 2 FROM DUAL) T3
ON (T1.C1 = T2.C2)
直接看执行计划
SQL> SELECT T1.C1, T2.C2, T3.C3
2 FROM T1
3 JOIN T2
4 ON (1 = 1)
5 LEFT JOIN (SELECT 1 AS C3 FROM DUAL UNION ALL SELECT 2 FROM DUAL) T3
6 ON (T1.C1 = T2.C2);
执行计划
----------------------------------------------------------
Plan hash value: 769723218
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 68 | 14 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 4 | 68 | 14 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 2 | 28 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 1 | 7 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 2 | 14 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T1 | 2 | 14 | 3 (0)| 00:00:01 |
| 6 | VIEW | | 2 | 6 | 4 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | VIEW | | 2 | 6 | 4 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("T1"."C1"="T2"."C2")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
个人的理解：1 从id=2 中可以看到,t1和t2是做了笛卡尔积
2 id=7 中可以看到,关联t3的条件 filter("T1"."C1"="T2"."C2") 有点不好理解，但确实是符合语法
先验证下，我的id=2的猜想是否正确
可以看到笛卡尔积的猜想是对的，最终的结果如下：
可以看到,是对关联后的"1,1"又进行了一次笛卡尔积，因为关联条件中没有t3的限制条件。
结果中会有第三行，是因为关联t3用的是left join。如果换成内连接,是不是第三行就没有了？
结果验证正确
