📄 lojreorder.out
字号:
-----------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> select * from (t inner join s on (b = e)) left outer join r on (f = i) where a > 0;A |B |C |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 t, s left outer join r on (d = g) where a = e;A |B |C |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 t left outer join s on (b = e), r where a = g;A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> -- bug 5658-- In DB2 UDB, any column referenced in an expression of the -- join-condition must be a column of one of the operand tables of -- the associated join (in the scope of the same joined-table clause).-- this query should failselect t1.*, s2.* from t t1 left outer join s on (b = e), t t2 left outer join s s2 on (b = e);ERROR 42X03: Column name 'B' is in more than one table in the FROM list.ij> -- a view of a regular joincreate view jv (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> select * from jv left outer join r on (fv = i);FV |EV |DV |CV |BV |AV |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 r left outer join jv on (fv = i);G |H |I |FV |EV |DV |CV |BV |AV -----------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL 5 |5.000 |5 |NULL |NULL |NULL |NULL |NULL |NULL ij> -- a view of LOJcreate view lojv (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> select * from r left outer join lojv on (fv = i);G |H |I |FV |EV |DV |CV |BV |AV -----------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL 5 |5.000 |5 |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from r right outer join lojv on (fv = i);G |H |I |FV |EV |DV |CV |BV |AV -----------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |1 |1.000 |1 NULL |NULL |NULL |2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> -- a view on top of a LOJ viewcreate view vv as (select * from lojv);0 rows inserted/updated/deletedij> select * from r left outer join vv on (fv = i);G |H |I |FV |EV |DV |CV |BV |AV -----------------------------------------------------------------------------------------------3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 4 |4.000 |4 |NULL |NULL |NULL |NULL |NULL |NULL 5 |5.000 |5 |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from r right outer join vv on (fv = i);G |H |I |FV |EV |DV |CV |BV |AV -----------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL |NULL |1 |1.000 |1 NULL |NULL |NULL |2 |2.000 |2 |2 |2.000 |2 3 |3.000 |3 |3 |3.000 |3 |3 |3.000 |3 ij> -- check for ON predicate for LOJ reordering: no reorderingselect * from t left outer join (s left outer join r on (f = i)) on (b = e and a > b);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |3.000 |3 |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t left outer join (s left outer join r on (f = i)) on (b = e and a = b);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 * from t left outer join (s left outer join r on (f = i)) on (b = e and 1 = 1);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 * from t left outer join (s left outer join r on (f = i)) on (b > e);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |NULL |NULL |NULL |NULL |NULL |NULL 2 |2.000 |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |3.000 |3 |2 |2.000 |2 |NULL |NULL |NULL ij> select * from t left outer join (s left outer join r on (f = i)) on (a = a);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |2 |2.000 |2 |NULL |NULL |NULL 1 |1.000 |1 |3 |3.000 |3 |3 |3.000 |3 1 |1.000 |1 |4 |4.000 |4 |4 |4.000 |4 2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 2 |2.000 |2 |3 |3.000 |3 |3 |3.000 |3 2 |2.000 |2 |4 |4.000 |4 |4 |4.000 |4 3 |3.000 |3 |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 |4 |4.000 |4 ij> select * from t left outer join (s left outer join r on (f = i)) on (a = 1);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |2 |2.000 |2 |NULL |NULL |NULL 1 |1.000 |1 |3 |3.000 |3 |3 |3.000 |3 1 |1.000 |1 |4 |4.000 |4 |4 |4.000 |4 2 |2.000 |2 |NULL |NULL |NULL |NULL |NULL |NULL 3 |3.000 |3 |NULL |NULL |NULL |NULL |NULL |NULL ij> select * from t left outer join (s left outer join r on (f = i)) on (1 = 1);A |B |C |D |E |F |G |H |I -----------------------------------------------------------------------------------------------1 |1.000 |1 |2 |2.000 |2 |NULL |NULL |NULL 1 |1.000 |1 |3 |3.000 |3 |3 |3.000 |3 1 |1.000 |1 |4 |4.000 |4 |4 |4.000 |4 2 |2.000 |2 |2 |2.000 |2 |NULL |NULL |NULL 2 |2.000 |2 |3 |3.000 |3 |3 |3.000 |3 2 |2.000 |2 |4 |4.000 |4 |4 |4.000 |4 3 |3.000 |3 |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 |4 |4.000 |4 ij> -- check for ON predicate for LOJ reordering: reordering is doneselect * from t left outer join (s left outer join r on (f = i)) on (a = d and b = e and c = f);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 * from t left outer join (s left outer join r on (f = i)) on ((a = d and b = e) and c = f);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 * from t left outer join (s left outer join r on (f = i)) on (a = d and (b = e and c = f));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 * from t left outer join (s left outer join r on (f = i)) on (not(a <> d or b <> e or c <> f));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> -- check for LOJ reorder with subqueryselect * from t left outer join (s left outer join r on (f = i)) on (a = d) where a in (select j from tt);A |B |C |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> -- LOJ involving the same tableselect * from t t1 left outer join (t t2 left outer join t t3 on (t2.a=t3.a)) on (t1.a=t2.a);A |B |C |A |B |C |A |B |C ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -