📄 subquery2.out
字号:
I -----------1 ij> select * from s_3rows where NOT s_3rows.i >= ANY (select i from v_empty);I -----------NULL 1 2 ij> select * from s_3rows where s_3rows.i < ALL (select i from v_empty);I -----------NULL 1 2 ij> -- NOT > ANY <=> <= ALLselect * from s_3rows where NOT s_3rows.i > ANY (select i from w_2);I -----------1 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from w_2);I -----------1 2 ij> select * from s_3rows where NOT s_3rows.i > ANY (select i from v_empty);I -----------NULL 1 2 ij> select * from s_3rows where s_3rows.i <= ALL (select i from v_empty);I -----------NULL 1 2 ij> -- NOT <= ANY <=> > ALLselect * from s_3rows where NOT s_3rows.i <= ANY (select i from w_2);I -----------ij> select * from s_3rows where s_3rows.i > ALL (select i from w_2);I -----------ij> select * from s_3rows where NOT s_3rows.i <= ANY (select i from v_empty);I -----------NULL 1 2 ij> select * from s_3rows where s_3rows.i > ALL (select i from v_empty);I -----------NULL 1 2 ij> -- NOT < ANY <=> >= ALLselect * from s_3rows where NOT s_3rows.i < ANY (select i from w_2);I -----------2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from w_2);I -----------2 ij> select * from s_3rows where NOT s_3rows.i < ANY (select i from v_empty);I -----------NULL 1 2 ij> select * from s_3rows where s_3rows.i >= ALL (select i from v_empty);I -----------NULL 1 2 ij> -- NOT = ALL <=> <> ANYselect * from s_3rows where NOT s_3rows.i = ALL (select i from w_2);I -----------1 ij> select * from s_3rows where s_3rows.i <> ANY (select i from w_2);I -----------1 ij> select * from s_3rows where NOT s_3rows.i = ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i <> ANY (select i from v_empty);I -----------ij> -- NOT <> ALL <=> = ANYselect * from s_3rows where NOT s_3rows.i <> ALL (select i from w_2);I -----------2 ij> select * from s_3rows where s_3rows.i = ANY (select i from w_2);I -----------2 ij> select * from s_3rows where NOT s_3rows.i <> ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i = ANY (select i from v_empty);I -----------ij> -- NOT >= ALL <=> < ANYselect * from s_3rows where NOT s_3rows.i >= ALL (select i from w_2);I -----------1 ij> select * from s_3rows where s_3rows.i < ANY (select i from w_2);I -----------1 ij> select * from s_3rows where NOT s_3rows.i >= ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i < ANY (select i from v_empty);I -----------ij> -- NOT > ALL <=> <= ANYselect * from s_3rows where NOT s_3rows.i > ALL (select i from w_2);I -----------1 2 ij> select * from s_3rows where s_3rows.i <= ANY (select i from w_2);I -----------1 2 ij> select * from s_3rows where NOT s_3rows.i > ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i <= ANY (select i from v_empty);I -----------ij> -- NOT <= ALL <=> > ANYselect * from s_3rows where NOT s_3rows.i <= ALL (select i from w_2);I -----------ij> select * from s_3rows where s_3rows.i > ANY (select i from w_2);I -----------ij> select * from s_3rows where NOT s_3rows.i <= ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i > ANY (select i from v_empty);I -----------ij> -- NOT < ALL <=> >= ANYselect * from s_3rows where NOT s_3rows.i < ALL (select i from w_2);I -----------2 ij> select * from s_3rows where s_3rows.i >= ANY (select i from w_2);I -----------2 ij> select * from s_3rows where NOT s_3rows.i < ALL (select i from v_empty);I -----------ij> select * from s_3rows where s_3rows.i >= ANY (select i from v_empty);I -----------ij> -- test skipping of generating is null predicates for non-nullable columnscreate table t1 (c1 int not null, c2 int);0 rows inserted/updated/deletedij> create table t2 (c1 int not null, c2 int);0 rows inserted/updated/deletedij> insert into t1 values(1, 2);1 row inserted/updated/deletedij> insert into t2 values(0, 3);1 row inserted/updated/deletedij> select * from t1 where c1 not in (select c2 from t2);C1 |C2 -----------------------1 |2 ij> select * from t1 where c2 not in (select c1 from t2);C1 |C2 -----------------------1 |2 ij> select * from t1 where c1 not in (select c1 from t2);C1 |C2 -----------------------1 |2 ij> drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> -- updatecreate table u (i int, s smallint, c char(30), vc char(30), b bigint);0 rows inserted/updated/deletedij> insert into u select * from s;3 rows inserted/updated/deletedij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- exists returns a boolean value and hence it can not be used to set a value. Exists can only be used in where clauseupdate u set b = exists (select * from t)where vc < ANY (select vc from s);ERROR 42X01: Syntax error: Encountered "exists" at line 2, column 18.ij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> delete from u;3 rows inserted/updated/deletedij> insert into u select * from s;3 rows inserted/updated/deletedij> -- deletedelete from u where c < ANY (select c from t);2 rows inserted/updated/deletedij> select * from u;I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL ij> -- do consistency check on scans, etc.values ConsistencyChecker();1 --------------------------------------------------------------------------------------------------------------------------------No open scans, etc.2 dependencies found ij> -- reset autocommitautocommit on;ij> -- drop the tablesdrop table s;0 rows inserted/updated/deletedij> drop table t;0 rows inserted/updated/deletedij> drop table tt;0 rows inserted/updated/deletedij> drop table ttt;0 rows inserted/updated/deletedij> drop table u;0 rows inserted/updated/deletedij> drop table s_3rows;0 rows inserted/updated/deletedij> drop table t_1;0 rows inserted/updated/deletedij> drop table u_null;0 rows inserted/updated/deletedij> drop table v_empty;0 rows inserted/updated/deletedij> drop table w_2;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -