📄 subquery.out
字号:
NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- constants in subquery select listselect * from s where i in (select 1 from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------1 |1 |1 |1 |1 ij> select * from s where i in (select -1 from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> select * from s where c in (select '1' from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------1 |1 |1 |1 |1 ij> select * from s where b in (select 0 from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 ij> -- constants on both sidesselect * from s where 1=1 in (select 0 from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> select * from s where 0 in (select 0 from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------NULL |NULL |NULL |NULL |NULL 0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- compatable typesselect * from s where c in (select vc from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where vc in (select c from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where i in (select s from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> select * from s where s in (select i from t);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- empty subquery result setselect * from s where i in (select i from t where 1 = 0);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> select * from s where (i in (select i from t where i = 0)) is null;I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> -- select listselect i in (select i from t) from s;ERROR 42X01: Syntax error: Encountered "in" at line 2, column 10.ij> select i in (select i from t where 1 = 0) from s;ERROR 42X01: Syntax error: Encountered "in" at line 1, column 10.ij> select (i in (select i from t where 1 = 0)) is null from s;ERROR 42X01: Syntax error: Encountered "in" at line 1, column 11.ij> -- subquery under an orselect i from s where i = -1 or i in (select i from t);I -----------0 1 ij> select i from s where i = 0 or i in (select i from t where i = -1);I -----------0 ij> select i from s where i = -1 or i in (select i from t where i = -1 or i = 1);I -----------1 ij> -- distinct eliminationselect i from s where i in (select i from s);I -----------0 1 ij> select i from s where i in (select distinct i from s);I -----------0 1 ij> select i from s ss where i in (select i from s where s.i = ss.i);I -----------0 1 ij> select i from s ss where i in (select distinct i from s where s.i = ss.i);I -----------0 1 ij> -- do consistency check on scans, etc.values ConsistencyChecker();1 --------------------------------------------------------------------------------------------------------------------------------No open scans, etc.2 dependencies found ij> -- correlated subqueries-- negative tests-- multiple matches at parent levelselect * from s, t where exists (select i from tt);ERROR 42X03: Column name 'I' is in more than one table in the FROM list.ij> -- match is against base table, but not derived column listselect * from s ss (c1, c2, c3, c4, c5) where exists (select i from tt);ERROR 42X04: Column 'I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table.ij> select * from s ss (c1, c2, c3, c4, c5) where exists (select ss.i from tt);ERROR 42X04: Column 'SS.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SS.I' is not a column in the target table.ij> -- correlation name exists at both levels, but only column match is at-- parent levelselect * from s where exists (select s.i from tt s);ERROR 42X04: Column 'S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table.ij> -- only match is at peer levelselect * from s where exists (select * from tt) and exists (select ii from t);ERROR 42X04: Column 'II' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'II' is not a column in the target table.ij> select * from s where exists (select * from tt) and exists (select tt.ii from t);ERROR 42X04: Column 'TT.II' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'TT.II' is not a column in the target table.ij> -- correlated column in a derived tableselect * from s, (select * from tt where i = ii) a;ERROR 42X04: Column 'I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'I' is not a column in the target table.ij> select * from s, (select * from tt where s.i = ii) a;ERROR 42X04: Column 'S.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.I' is not a column in the target table.ij> -- positive tests-- simple correlated subqueriesselect (select i from tt where ii = i and ii <> 1) from s;1 -----------NULL 0 NULL ij> select (select s.i from tt where ii = s.i and ii <> 1) from s;1 -----------NULL 0 NULL ij> select (select s.i from ttt where iii = i) from s;1 -----------NULL NULL NULL ij> select * from s where exists (select * from tt where i = ii and ii <> 1);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 ij> select * from s where exists (select * from tt where s.i = ii and ii <> 1);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 ij> select * from s where exists (select * from ttt where i = iii);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> -- 1 case where we get a cardinality violation after a few rowsselect (select i from tt where ii = i) from s;1 -----------NULL 0 ERROR 21000: Scalar subquery is only allowed to return a single row.ij> -- skip levels to find matchselect * from s where exists (select * from ttt where iii = (select 11 from tt where ii = i and ii <> 1));I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 ij> -- join in subqueryselect * from s where i in (select i from t, tt where s.i <> i and i = ii);I |S |C |VC |B -----------------------------------------------------------------------------------------------------ij> select * from s where i in (select i from t, ttt where s.i < iii and s.i = t.i);I |S |C |VC |B -----------------------------------------------------------------------------------------------------0 |0 |0 |0 |0 1 |1 |1 |1 |1 ij> -- join in outer query blockselect s.i, t.i from s, t where exists (select * from ttt where iii = 1);I |I -----------------------ij> select s.i, t.i from s, t where exists (select * from ttt where iii = 11);I |I -----------------------NULL |NULL NULL |0 NULL |1 NULL |1 NULL |2 0 |NULL 0 |0 0 |1 0 |1 0 |2 1 |NULL 1 |0 1 |1 1 |1 1 |2 ij> -- joins in both query blocks⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -