连接探索（交叉连接，内连接，外连接）

创建临时表

select * into #t1 from (
select 0 num union all
select 1 num union all
select 2 num union all
select 3 num union all
select 4 num union all
select 5 num union all
select 6 num union all
select 7 num union all
select 8 num union all
select 9 num
) T;
select * into #t2 from (
select 0 num union all
select 1 num union all
select 2 num union all
select 3 num union all
select 4 num union all
select 5 num 
) T;

交叉连接，内连接，外连接探索

交叉连接 笛卡尔积
内连接 笛卡尔积+过滤
外连接 笛卡尔积+过滤+添加外部行(用NULL作占位符)

--CROSS JOIN
select * from #t1 a cross join #t2 b;--cartesian product
/*
0   0
1   0
2   0
3   0
4   0
5   0
6   0
7   0
8   0
9   0
0   1
1   1
2   1
3   1
4   1
5   1
6   1
7   1
8   1
9   1
0   2
1   2
2   2
3   2
4   2
5   2
6   2
7   2
8   2
9   2
0   3
1   3
2   3
3   3
4   3
5   3
6   3
7   3
8   3
9   3
0   4
1   4
2   4
3   4
4   4
5   4
6   4
7   4
8   4
9   4
0   5
1   5
2   5
3   5
4   5
5   5
6   5
7   5
8   5
9   5
*/

--JOIN
select a.num as anum ,b.num as bnum from #t1 a join #t2 b on a.num=b.num
/*
0   0
1   1
2   2
3   3
4   4
5   5
*/
--实现步骤拆解
select * from (
select  a.num as anum ,b.num as bnum from #t1 a cross join #t2 b --Step 1 :cartesian product
) T
where anum=bnum --Step2 :filter
;
/*
0   0
1   1
2   2
3   3
4   4
5   5
*/

--outer join
select a.num as anum ,b.num as bnum from #t1 a left outer join #t2 b on a.num=b.num
/*
0   0
1   1
2   2
3   3
4   4
5   5
6   NULL
7   NULL
8   NULL
9   NULL
*/
--实现步骤拆解
select * from (
select  a.num as anum ,b.num as bnum from #t1 a cross join #t2 b --Step 1 :cartesian product
) T
where anum=bnum --Step2 :filter
union all
-- Step3 : add outer column
select A.num anum, NULL bnum from #t1 A where a.num not in ( select anum from (
select  a.num as anum ,b.num as bnum from #t1 a cross join #t2 b --Step 1 :cartesian product
) T
where anum=bnum --Step2 :filter
);
/*
0   0
1   1
2   2
3   3
4   4
5   5
6   NULL
7   NULL
8   NULL
9   NULL
*/

OUTER JOIN中过滤条件失效的问题及解析

--下面外连接的结果以及生产逻辑
select * from #t1 a left join #t2 b on (a.num=b.num and b.num=1);
/*
0   NULL
1   1
2   NULL
3   NULL
4   NULL
5   NULL
6   NULL
7   NULL
8   NULL
9   NULL
*/
select * from #t1 a left join #t2 b on (a.num=b.num and a.num=1);
/*
0   NULL
1   1
2   NULL
3   NULL
4   NULL
5   NULL
6   NULL
7   NULL
8   NULL
9   NULL
*/
select * from #t1 a left join #t2 b on (a.num=b.num and a.num=1 and b.num=2);
/*
0   NULL
1   NULL
2   NULL
3   NULL
4   NULL
5   NULL
6   NULL
7   NULL
8   NULL
9   NULL
*/
select * from #t1 a left join #t2 b on (a.num=b.num and a.num=1 and b.num=1);
/*
0   NULL
1   1
2   NULL
3   NULL
4   NULL
5   NULL
6   NULL
7   NULL
8   NULL
9   NULL
*/
--因为需要添加外部行，所以#t1的过滤条件无法真正对#t1进行过滤，若要过滤需要在where条件里面再增加一层过滤

--原SQL
select * from #t1 a left join #t2 b on (a.num=b.num and a.num=1 and b.num=1);
--真正理解外连接逻辑之后不难理解，实现步骤拆解
select * from (
select  a.num as anum ,b.num as bnum from #t1 a cross join #t2 b --Step 1 :cartesian product
) T
where anum=bnum and anum=1 and bnum=1--Step2 :filter
union all
-- Step3 : add outer column
select A.num anum, NULL bnum from #t1 A where a.num not in ( select anum from (
select  a.num as anum ,b.num as bnum from #t1 a cross join #t2 b --Step 1 :cartesian product
) T
where anum=bnum and anum=1 and bnum=1--Step2 :filter
);

注意：外连接后面跟内连接或右外连接，都会抵消外连接的外部行（NULL被过滤掉）。

--外连接外部行被过滤问题
select * from #t1 a left join #t2 b on a.num=b.num join #t2 c on c.num=b.num
/*
0   0   0
1   1   1
2   2   2
3   3   3
4   4   4
5   5   5
*/
--原SQL，由于后一步join涉及到对b.num的过滤，而a添加的外部行的b表字段的占位符是NULL，会被过滤掉

--不过滤掉外部行的逻辑实现
select * from #t2 b join #t2 c on c.num=b.num right join #t1 a on a.num=b.num --way1
select * from #t1 a left join (#t2 b join #t2 c on c.num=b.num) on a.num=b.num --way2
/*
0   0   0
1   1   1
2   2   2
3   3   3
4   4   4
5   5   5
6   NULL    NULL
7   NULL    NULL
8   NULL    NULL
9   NULL    NULL
*/