📄 lojreorder.out
字号:
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 g > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select j+1, k+2, L||'s', a+10, b+10, C||'t', d+20, e+20, f||'u', g+30, h+30, i||'v' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12 ---------------------------------------------------------------------------------------------------------------------------------------------------3 |4.000 |2s |12 |12.000 |2t |22 |22.000 |2u |NULL |NULL |NULL 4 |5.000 |3s |13 |13.000 |3t |23 |23.000 |3u |33 |33.000 |3v 5 |6.000 |4s |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> select j, j+1, k, k+2, L, L||'s' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);J |2 |K |4 |L |6 --------------------------------------------------------------------2 |3 |2.000 |4.000 |2 |2s 3 |4 |3.000 |5.000 |3 |3s 4 |5 |4.000 |6.000 |4 |4s ij> select a, a+10, b, b+10, C, C||'t' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);A |2 |B |4 |C |6 --------------------------------------------------------------------2 |12 |2.000 |12.000 |2 |2t 3 |13 |3.000 |13.000 |3 |3t NULL |NULL |NULL |NULL |NULL |NULL ij> select d, d+20, e, e+20, f, f||'u' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);D |2 |E |4 |F |6 --------------------------------------------------------------------2 |22 |2.000 |22.000 |2 |2u 3 |23 |3.000 |23.000 |3 |3u NULL |NULL |NULL |NULL |NULL |NULL ij> select g, g+30, h, h+30, i, i||'v' from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=a);G |2 |H |4 |I |6 --------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL 3 |33 |3.000 |33.000 |3 |3v 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=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> select * from tt left outer join (t left outer join s on (b=e) left outer join r on (f = i)) on (j=d) 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=d) 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=d) 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 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=d) where g > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------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=g);J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |NULL |NULL |NULL |NULL |NULL |NULL |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=g) where j > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------2 |2.000 |2 |NULL |NULL |NULL |NULL |NULL |NULL |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=g) where b > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------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=g) where e > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------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=g) where g > 0;J |K |L |A |B |C |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> ---- Create views of union, regular joins, subquery, group by, having clause,-- and use the view in the LOJ operands and yet the LOJ reordering is applicable.-- We need to test the join condition: LHS=RHS.create view v1 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t left outer join s on b = e);0 rows inserted/updated/deletedij> create view v2 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t inner join s on b = e);0 rows inserted/updated/deletedij> create view v3 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t right join s on b = e);0 rows inserted/updated/deletedij> create view v4 (fv, ev, dv, cv, bv, av) as (select f, e, d, c, b, a from t, s where b = e);0 rows inserted/updated/deletedij> create view v5 (cv, bv, cnt) as (select c, b, count(*) from t group by c, b);0 rows inserted/updated/deletedij> create view v6 (cv, bv, cnt) as (select c, b, count(*) from t group by c, b having b > 0);0 rows inserted/updated/deletedij> create view v7 (cv, bv, av) as (select c, b, a from t where b in (select e from s));0 rows inserted/updated/deletedij> create view v8 (cv, bv, av) as (select c, b, a from t union select f, e, d from s);0 rows inserted/updated/deletedij> -- All these test cases need table references to determine if the join condition is proper for LOJ-- reordering: LHS = RHSselect * from v1 left outer join (s left outer join r on (f = i)) on (d=v1.av);FV |EV |DV |CV |BV |AV |D |E |F |G |H |I -------------------------------------------------------------------------------------------------------------------------------NULL |NULL |NULL |1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 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 v2 left outer join (s left outer join r on (f = i)) on (d=v2.av);FV |EV |DV |CV |BV |AV |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 v3 left outer join (s left outer join r on (f = i)) on (d=v3.av);FV |EV |DV |CV |BV |AV |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 v4 left outer join (s left outer join r on (f = i)) on (d=v4.av);FV |EV |DV |CV |BV |AV |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 v5 left outer join (s left outer join r on (f = i)) on (e=v5.bv);CV |BV |CNT |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |1 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |1 |3 |3.000 |3 |3 |3.000 |3 ij> select * from v6 left outer join (s left outer join r on (f = i)) on (e=v6.bv);CV |BV |CNT |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |1 |2 |2.000 |2 |NULL |NULL |NULL 3 |3.000 |1 |3 |3.000 |3 |3 |3.000 |3 ij> select * from v7 left outer join (s left outer join r on (f = i)) on (e=v7.bv);CV |BV |AV |D |E |F |G |H |I -----------------------------------------------------------------------------------------------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 * from v8 left outer join (s left outer join r on (f = i)) on (e=v8.bv);CV |BV |AV |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 4 |4.000 |4 |4 |4.000 |4 |4 |4.000 |4 ij> select * from t left outer join (s left outer join v1 on (f = cv)) on (d=a);A |B |C |D |E |F |FV |EV |DV |CV |BV |AV -------------------------------------------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |1 |1.000 |1 |NULL |NULL |NULL 2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 |2 |2.000 |2 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -