📄 lojreorder.out
字号:
-----------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |1 |1.000 |1 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.a)) on (t1.b=t2.b);A |B |C |A |B |C |A |B |C -----------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |1 |1.000 |1 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.b)) on (t1.a=t2.b);A |B |C |A |B |C |A |B |C -----------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |1 |1.000 |1 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> -- comparing columns of different types.select t.a, s.d, r.g from t left outer join (s left outer join r on (e=g)) on (b=d);A |D |G -----------------------------------1 |NULL |NULL 2 |2 |NULL 3 |3 |3 ij> select r.g from t left outer join (s left outer join r on (e=g)) on (b=d);G -----------NULL NULL 3 ij> -- these seem to workselect * from t left outer join (s left outer join r on (e=g)) on (b=d);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select t.a from t left outer join (s left outer join r on (e=g)) on (b=d);A -----------1 2 3 ij> select s.f, s.e, s.d, t.c, t.b, t.a from t left outer join (s left outer join r on (e=g)) on (b=d);F |E |D |C |B |A ---------------------------------------------------------------NULL |NULL |NULL |1 |1.000 |1 2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 ij> -- need to test more on changes deep inside...create view loj (a, b, c, d, e, f, g, h, i, ae) as (select a, b, c, d, e, f, g, h, i, a+e as "A+E" from t left outer join (s left outer join r on (f = i)) on (b = e));0 rows inserted/updated/deletedij> select * from loj;A |B |C |D |E |F |G |H |I |AE -----------------------------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL |4.000 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |6.000 ij> select * from t left outer join loj on (t.a=loj.a);A |B |C |A |B |C |D |E |F |G |H |I |AE -------------------------------------------------------------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL |4.000 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |6.000 ij> select * from t left outer join loj on (t.a=loj.d);A |B |C |A |B |C |D |E |F |G |H |I |AE -------------------------------------------------------------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL |4.000 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |6.000 ij> select * from t right outer join loj on (t.a=loj.a);A |B |C |A |B |C |D |E |F |G |H |I |AE -------------------------------------------------------------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL |4.000 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |6.000 ij> select * from t inner join loj on (t.a=loj.a);A |B |C |A |B |C |D |E |F |G |H |I |AE -------------------------------------------------------------------------------------------------------------------------------------------------1 |1.000 |1 |1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL |4.000 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |6.000 ij> -- 4 level deep-- b=e will be pushed first, one level down-- j=a will then be pushed next, 2 levels down (after b=e pushed)select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a);J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a) where j>0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=a) where j>0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=d);J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> -- TT LOJ (T LOJ (S LOJ R))) becomes TT LOJ ((T LOJ S) LOJ R)select 1 from tt left outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=r.g);1 -----------1 1 1 ij> select 1 from tt right outer join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=g);1 -----------1 1 1 ij> -- okayselect 1 from tt, (t left outer join (s left outer join r on (f = i)) on (b = e)) where (j=g);1 -----------1 ij> select 1 from tt right outer join (t left outer join (s inner join r on (f = i)) on (b = e)) on (j=g);1 -----------1 1 1 ij> select 1 from tt inner join (t left outer join (s left outer join r on (f = i)) on (b = e)) on (j=g);1 -----------1 ij> -- LOJ w/ left-deep LOJ: LOJ will be pushed twiceselect * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where j > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where b > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a) where e > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -