📄 outerjoin.out
字号:
2 |NULL |NULL 2 |NULL |NULL 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from t3 right outer join t2 on t3.c1 = t2.c1 right outer join t1 on t1.c1 = t2.c1;C1 |C1 |C1 -----------------------------------NULL |1 |1 NULL |NULL |2 NULL |NULL |2 3 |3 |3 3 |3 |3 NULL |NULL |4 ij> -- parensselect * from (t1 left outer join t2 on t1.c1 = t2.c1) left outer join t3 on t1.c1 = t3.c1;C1 |C1 |C1 -----------------------------------1 |1 |NULL 2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> select * from t1 left outer join (t2 left outer join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1;C1 |C1 |C1 -----------------------------------1 |1 |NULL 2 |NULL |NULL 2 |NULL |NULL 3 |3 |3 3 |3 |3 4 |NULL |NULL ij> -- left/right outer join combinationsselect * from t1 a right outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;C1 |C1 |C1 -----------------------------------1 |1 |NULL 3 |3 |3 3 |3 |3 NULL |5 |NULL NULL |6 |NULL ij> select * from (t1 a right outer join t2 b on a.c1 = b.c1) left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;C1 |C1 |C1 -----------------------------------1 |1 |NULL 3 |3 |3 3 |3 |3 NULL |5 |NULL NULL |6 |NULL ij> select * from t1 a left outer join t2 b on a.c1 = b.c1 right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;C1 |C1 |C1 -----------------------------------2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 ij> select * from (t1 a left outer join t2 b on a.c1 = b.c1) right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;C1 |C1 |C1 -----------------------------------2 |NULL |2 2 |NULL |2 3 |3 |3 3 |3 |3 ij> select * from t1 a left outer join (t2 b right outer join t3 c on c.c1 = b.c1) on a.c1 = c.c1 where c.c1=b.c1;C1 |C1 |C1 -----------------------------------3 |3 |3 3 |3 |3 ij> -- test insert/update/deleteinsert into insert_testselect * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1;26 rows inserted/updated/deletedij> select * from insert_test;C1 |C2 |C3 -----------------------------------1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |5 1 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> update insert_testset c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1)where c1 = 1;5 rows inserted/updated/deletedij> select * from insert_test;C1 |C2 |C3 -----------------------------------9 |1 |2 9 |1 |3 9 |1 |5 9 |1 |5 9 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_testwhere c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1);5 rows inserted/updated/deletedij> select * from insert_test;C1 |C2 |C3 -----------------------------------2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_test;21 rows inserted/updated/deletedij> insert into insert_testselect * from (select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1) d (c1, c2, c3);26 rows inserted/updated/deletedij> select * from insert_test;C1 |C2 |C3 -----------------------------------1 |1 |2 1 |1 |3 1 |1 |5 1 |1 |5 1 |1 |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 2 |NULL |3 2 |NULL |5 2 |NULL |5 2 |NULL |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 3 |3 |2 3 |3 |5 3 |3 |5 3 |3 |7 4 |NULL |2 4 |NULL |3 4 |NULL |5 4 |NULL |5 4 |NULL |7 ij> delete from insert_test;26 rows inserted/updated/deletedij> -- verify that right outer join xforms don't get result columns-- confusedcreate table a (c1 int);0 rows inserted/updated/deletedij> create table b (c2 float);0 rows inserted/updated/deletedij> create table c (c3 char(30));0 rows inserted/updated/deletedij> insert into a values 1;1 row inserted/updated/deletedij> insert into b values 3.3;1 row inserted/updated/deletedij> insert into c values 'asdf';1 row inserted/updated/deletedij> select * from a left outer join b on 1=1 left outer join c on 1=1;C1 |C2 |C3 -----------------------------------------------------------------1 |3.3 |asdf ij> select * from a left outer join b on 1=1 left outer join c on 1=0;C1 |C2 |C3 -----------------------------------------------------------------1 |3.3 |NULL ij> select * from a left outer join b on 1=0 left outer join c on 1=1;C1 |C2 |C3 -----------------------------------------------------------------1 |NULL |asdf ij> select * from a left outer join b on 1=0 left outer join c on 1=0;C1 |C2 |C3 -----------------------------------------------------------------1 |NULL |NULL ij> select * from c right outer join b on 1=1 right outer join a on 1=1;C3 |C2 |C1 -----------------------------------------------------------------asdf |3.3 |1 ij> select * from c right outer join b on 1=1 right outer join a on 1=0;C3 |C2 |C1 -----------------------------------------------------------------NULL |NULL |1 ij> select * from c right outer join b on 1=0 right outer join a on 1=1;C3 |C2 |C1 -----------------------------------------------------------------NULL |3.3 |1 ij> select * from c right outer join b on 1=0 right outer join a on 1=0;C3 |C2 |C1 -----------------------------------------------------------------NULL |NULL |1 ij> -- multicolumn tests-- c1, c2, and c3 all have the same valuesselect tt1.c1, tt1.c2, tt1.c3, tt2.c2, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;C1 |C2 |C3 |C2 |C3 -----------------------------------------------------------1 |1 |1 |1 |1 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 3 |3 |3 |3 |3 3 |3 |3 |3 |3 4 |4 |4 |NULL |NULL ij> select tt1.c1, tt1.c2, tt1.c3, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;C1 |C2 |C3 |C3 -----------------------------------------------1 |1 |1 |1 2 |2 |2 |NULL 2 |2 |2 |NULL 3 |3 |3 |3 3 |3 |3 |3 4 |4 |4 |NULL ij> select tt1.c1, tt1.c2, tt1.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;C1 |C2 |C3 -----------------------------------1 |1 |1 2 |2 |2 2 |2 |2 3 |3 |3 3 |3 |3 4 |4 |4 ij> -- nested outer joinsselect tt1.c2, tt1.c1, tt1.c3, tt2.c1, tt2.c3 from t1 left outer join tt1 on t1.c1 = tt1.c1 left outer join tt2 on tt1.c2 = tt2.c2;C2 |C1 |C3 |C1 |C3 -----------------------------------------------------------1 |1 |1 |1 |1 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 2 |2 |2 |NULL |NULL 3 |3 |3 |3 |3 3 |3 |3 |3 |3 4 |4 |4 |NULL |NULL ij> -- make sure that column reordering is working correctly -- when there's an ON clausecreate table x (c1 int, c2 int, c3 int);0 rows inserted/updated/deletedij> create table y (c3 int, c4 int, c5 int);0 rows inserted/updated/deletedij> insert into x values (1, 2, 3), (4, 5, 6);2 rows inserted/updated/deletedij> insert into y values (3, 4, 5), (666, 7, 8);2 rows inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -