Predicates
Primitive predicates and boolean connectives. Tests project the predicate through SELECT so the truth value is shown directly. A small section at the end pins the WHERE-boundary rule that a null predicate excludes the row. Scope: comparisons with null, IS [NOT] NULL, IS [NOT] {TRUE|FALSE|UNKNOWN}, AND/OR/NOT, BETWEEN, IN-list.
Is null True
Shows the result of is null true.
SQL
create table T;
insert into T ({});
select null is null from T;
Result
[
true
]
Is null False
Shows the result of is null false.
SQL
create table T;
insert into T ({});
select 1 is null from T;
Result
[
false
]
Is Not null
Shows the result of is not null on null.
SQL
create table T;
insert into T ({});
select null is not null from T;
Result
[
false
]
Is Not null
Shows the result of is not null on value.
SQL
create table T;
insert into T ({});
select 1 is not null from T;
Result
[
true
]
Is null On
Shows the result of is null on null column.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({x: null});
select s.x is null from S as s;
Result
[
true
]
Is null On
Shows the result of is null on absent key.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({});
select s.x is null from S as s;
Result
[
true
]
Is Not null
Shows the result of is not null on present key.
SQL
create table T;
insert into T ({});
create table S;
insert into S ({x: 1});
select s.x is not null from S as s;
Result
[
true
]
True Is True
Shows the result of true is true.
SQL
create table T;
insert into T ({});
select true is true from T;
Result
[
true
]
False Is True
Shows the result of false is true.
SQL
create table T;
insert into T ({});
select false is true from T;
Result
[
false
]
null Is True
Shows the result of null is true.
SQL
create table T;
insert into T ({});
select null is true from T;
Result
[
false
]
True Is False
Shows the result of true is false.
SQL
create table T;
insert into T ({});
select true is false from T;
Result
[
false
]
False Is False
Shows the result of false is false.
SQL
create table T;
insert into T ({});
select false is false from T;
Result
[
true
]
null Is False
Shows the result of null is false.
SQL
create table T;
insert into T ({});
select null is false from T;
Result
[
false
]
True Is Unknown
Shows the result of true is unknown.
SQL
create table T;
insert into T ({});
select true is unknown from T;
Result
[
false
]
False Is Unknown
Shows the result of false is unknown.
SQL
create table T;
insert into T ({});
select false is unknown from T;
Result
[
false
]
null Is Unknown
Shows the result of null is unknown.
SQL
create table T;
insert into T ({});
select null is unknown from T;
Result
[
true
]
Not True
Shows the result of true is not true.
SQL
create table T;
insert into T ({});
select true is not true from T;
Result
[
false
]
Not True
Shows the result of null is not true.
SQL
create table T;
insert into T ({});
select null is not true from T;
Result
[
true
]
Not False
Shows the result of null is not false.
SQL
create table T;
insert into T ({});
select null is not false from T;
Result
[
true
]
Not Unknown
Shows the result of null is not unknown.
SQL
create table T;
insert into T ({});
select null is not unknown from T;
Result
[
false
]
Not True
Shows the result of not true.
SQL
create table T;
insert into T ({});
select not true from T;
Result
[
false
]
Not False
Shows the result of not false.
SQL
create table T;
insert into T ({});
select not false from T;
Result
[
true
]
Not null
Shows the result of not null.
SQL
create table T;
insert into T ({});
select not null from T;
Result
[
null
]
Double Not
Shows the result of double not.
SQL
create table T;
insert into T ({});
select not not true from T;
Result
[
true
]
And T T
Shows the result of and t t.
SQL
create table T;
insert into T ({});
select true and true from T;
Result
[
true
]
And T F
Shows the result of and t f.
SQL
create table T;
insert into T ({});
select true and false from T;
Result
[
false
]
And T N
Shows the result of and t n.
SQL
create table T;
insert into T ({});
select true and null from T;
Result
[
null
]
And F T
Shows the result of and f t.
SQL
create table T;
insert into T ({});
select false and true from T;
Result
[
false
]
And F F
Shows the result of and f f.
SQL
create table T;
insert into T ({});
select false and false from T;
Result
[
false
]
False-dominance, False
False-dominance, false AND null is false.
SQL
create table T;
insert into T ({});
select false and null from T;
Result
[
false
]
And N T
Shows the result of and n t.
SQL
create table T;
insert into T ({});
select null and true from T;
Result
[
null
]
False-dominance, Null
False-dominance, null AND false is false.
SQL
create table T;
insert into T ({});
select null and false from T;
Result
[
false
]
And N N
Shows the result of and n n.
SQL
create table T;
insert into T ({});
select null and null from T;
Result
[
null
]
Or T T
Shows the result of or t t.
SQL
create table T;
insert into T ({});
select true or true from T;
Result
[
true
]
Or T F
Shows the result of or t f.
SQL
create table T;
insert into T ({});
select true or false from T;
Result
[
true
]
True-dominance, True
True-dominance, true OR null is true.
SQL
create table T;
insert into T ({});
select true or null from T;
Result
[
true
]
Or F T
Shows the result of or f t.
SQL
create table T;
insert into T ({});
select false or true from T;
Result
[
true
]
Or F F
Shows the result of or f f.
SQL
create table T;
insert into T ({});
select false or false from T;
Result
[
false
]
Or F N
Shows the result of or f n.
SQL
create table T;
insert into T ({});
select false or null from T;
Result
[
null
]
True-dominance, Null
True-dominance, null OR true is true.
SQL
create table T;
insert into T ({});
select null or true from T;
Result
[
true
]
Or N F
Shows the result of or n f.
SQL
create table T;
insert into T ({});
select null or false from T;
Result
[
null
]
Or N N
Shows the result of or n n.
SQL
create table T;
insert into T ({});
select null or null from T;
Result
[
null
]
Than Or
A OR b AND c parses as a OR (b AND c).
SQL
create table T;
insert into T ({});
select false or true and false from T;
Result
[
false
]
NOT A
NOT a AND b parses as (NOT a) AND b.
SQL
create table T;
insert into T ({});
select not false and true from T;
Result
[
true
]
Parens Override Precedence
Shows the result of parens override precedence.
SQL
create table T;
insert into T ({});
select (false or true) and false from T;
Result
[
false
]
Between Inside
Shows the result of between inside.
SQL
create table T;
insert into T ({});
select 5 between 1 and 10 from T;
Result
[
true
]
Between Low Boundary
Shows the result of between low boundary.
SQL
create table T;
insert into T ({});
select 1 between 1 and 10 from T;
Result
[
true
]
Between High Boundary
Shows the result of between high boundary.
SQL
create table T;
insert into T ({});
select 10 between 1 and 10 from T;
Result
[
true
]
Between Below Range
Shows the result of between below range.
SQL
create table T;
insert into T ({});
select 0 between 1 and 10 from T;
Result
[
false
]
Between Above Range
Shows the result of between above range.
SQL
create table T;
insert into T ({});
select 11 between 1 and 10 from T;
Result
[
false
]
Asymmetric BETWEEN,
Asymmetric BETWEEN, a > b yields false.
SQL
create table T;
insert into T ({});
select 5 between 10 and 1 from T;
Result
[
false
]
Not Between Inside
Shows the result of not between inside.
SQL
create table T;
insert into T ({});
select 5 not between 1 and 10 from T;
Result
[
false
]
Not Between Outside
Shows the result of not between outside.
SQL
create table T;
insert into T ({});
select 11 not between 1 and 10 from T;
Result
[
true
]
Between On Column
Shows the result of between on column.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 5});
select n.x between 1 and 10 from N as n;
Result
[
true
]
Strings Order Lexicographically
Strings order lexicographically.
SQL
create table T;
insert into T ({});
select 'a' < 'b' from T;
Result
[
true
]
String Gt
Shows the result of string gt.
SQL
create table T;
insert into T ({});
select 'b' > 'a' from T;
Result
[
true
]
String Ge Equal
Shows the result of string ge equal.
SQL
create table T;
insert into T ({});
select 'a' >= 'a' from T;
Result
[
true
]
String Between Inside
Shows the result of string between inside.
SQL
create table T;
insert into T ({});
select 'b' between 'a' and 'c' from T;
Result
[
true
]
String Between Outside
Shows the result of string between outside.
SQL
create table T;
insert into T ({});
select 'd' between 'a' and 'c' from T;
Result
[
false
]
In Hit
Shows the result of in hit.
SQL
create table T;
insert into T ({});
select 2 in (1, 2, 3) from T;
Result
[
true
]
In Miss
Shows the result of in miss.
SQL
create table T;
insert into T ({});
select 99 in (1, 2, 3) from T;
Result
[
false
]
In Single Element
Shows the result of in single element.
SQL
create table T;
insert into T ({});
select 1 in (1) from T;
Result
[
true
]
In String
Shows the result of in string.
SQL
create table T;
insert into T ({});
select 'bob' in ('alice', 'bob', 'carol') from T;
Result
[
true
]
Not In Hit
Shows the result of not in hit.
SQL
create table T;
insert into T ({});
select 2 not in (1, 2, 3) from T;
Result
[
false
]
Not In Miss
Shows the result of not in miss.
SQL
create table T;
insert into T ({});
select 99 not in (1, 2, 3) from T;
Result
[
true
]
In On Column
Shows the result of in on column.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 2});
select n.x in (1, 2, 3) from N as n;
Result
[
true
]
Pin Existing
Pin existing semantics, null = null is true.
SQL
create table T;
insert into T ({});
select null = null from T;
Result
[
true
]
Pin Existing
Pin existing semantics, null = 1 is false.
SQL
create table T;
insert into T ({});
select null = 1 from T;
Result
[
false
]
Pin Existing
Pin existing semantics, null contaminates ne to false.
SQL
create table T;
insert into T ({});
select null != 1 from T;
Result
[
false
]
Pin Existing
Pin existing semantics, null != null is false.
SQL
create table T;
insert into T ({});
select null != null from T;
Result
[
false
]
Pin Existing
Pin existing semantics, ordering with null is false.
SQL
create table T;
insert into T ({});
select null < 1 from T;
Result
[
false
]
Where Boundary
A null predicate result excludes the row at the where boundary.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 1});
select n.x from N as n where null and n.x > 0;
Result
[]
Includes All
Shows the result of where true includes all.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 1}, {x: 2});
select n.x from N as n where true or null;
Result
[
1,
2
]
Where And Filters
Shows the result of where and filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 2, y: 5});
select n.x from N as n where n.x > 1 and n.y > 0;
Result
[
2
]
Where Or Filters
Shows the result of where or filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 3});
select n.x from N as n where n.x = 1 or n.x = 3;
Result
[
3
]
null Filters
Shows the result of where is null filters.
SQL
create table T;
insert into T ({});
create table N;
insert into N ({x: 1}, {x: null}, {x: 3});
select * from N where N.x is null;
Result
[
{ "x": null }
]
Two Identical
Two identical array literals are equal.
SQL
create table T;
insert into T ({});
select [1] = [1] from T;
Result
[
true
]
Two Distinct
Two distinct array literals are not equal.
SQL
create table T;
insert into T ({});
select [1] = [2] from T;
Result
[
false
]
Ne On
Ne on unequal arrays is true.
SQL
create table T;
insert into T ({});
select [1] != [2] from T;
Result
[
true
]
Ne On
Ne on equal arrays is false.
SQL
create table T;
insert into T ({});
select [1, 2] != [1, 2] from T;
Result
[
false
]
In Where
A non-empty array is truthy in a Where clause.
SQL
create table T;
insert into T ({});
create table W;
insert into W ({v: 42});
select w.v from W as w where [1];
Result
[
42
]