Where

The Where clause filters the binding-tuple stream by a boolean predicate. Only tuples for which the predicate evaluates to exactly true pass through; false and null both drop the row.

Syntax

Railroad

whereexpr

BNF

where-clause ::= "where" expr

Rules

  1. The predicate must evaluate to bool. A null result is treated as not-true and drops the tuple. (phase: after from / with, before group, order by, limit, and select)
  2. Comparison with null follows SQL three-valued logic: only null = null is true; null compared to a non-null value is false; ordering null against non-null yields null (drops the row in where). (phase: after from)
  3. Aggregate functions may not appear in the where predicate. (phase: after from)

Examples

Minimal

Constant True

Constant true keeps all rows.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2});

select * from T where true;

Result

[
  { "x": 1 },
  { "x": 2 }
]

Constant False

Constant false drops all rows.

SQL

create table T;

insert into T ({"x": 1});

select * from T where false;

Result

[]

Null Predicate

Null predicate is not-true and drops all rows.

SQL

create table T;

insert into T ({"x": 1});

select * from T where null;

Result

[]

Numeric Greater-than

Numeric greater-than filters by oid insertion order.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T where T.x > 1;

Result

[
  { "x": 2 },
  { "x": 3 }
]

Numeric Equality

Numeric equality matches a single row.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T where T.x = 2;

Result

[
  { "x": 2 }
]

Numeric Inequality

Numeric inequality excludes matching value.

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select * from T where T.x != 1;

Result

[
  { "x": 2 },
  { "x": 3 }
]

String Equality

String equality in where.

SQL

create table T;

create table S;

insert into S ({"name": 'alice'}, {"name": 'bob'});

select * from S where S.name = 'bob';

Result

[
  { "name": "bob" }
]

Boolean Equality

Boolean equality in where.

SQL

create table T;

insert into T ({"flag": true}, {"flag": false});

select * from T where T.flag = true;

Result

[
  { "flag": true }
]

Predicate May

Predicate may use an explicit from alias.

SQL

create table T;

insert into T ({"x": 0}, {"x": 1});

select * from T as t where t.x > 0;

Result

[
  { "x": 1 }
]

Under 3VL,

Under 3VL, x = null is unknown so the row is excluded (use IS NULL).

SQL

create table T;

insert into T ({"x": null});

select * from T where T.x = null;

Result

[]

Null Member

Null member fails inequality against non-null.

SQL

create table T;

insert into T ({"x": null});

select * from T where T.x != 1;

Result

[]

Absent Field

Absent field reads as null, so x = null is unknown and excluded.

SQL

create table T;

insert into T ({});

select * from T where T.x = null;

Result

[]

Only Rows

Only rows with present matching field pass equality.

SQL

create table T;

insert into T ({"x": 1}, {});

select * from T where T.x = 1;

Result

[
  { "x": 1 }
]

Scalar Projection

Scalar projection with where (moved from from-clause suite).

SQL

create table T;

insert into T ({"x": 1}, {"x": 2}, {"x": 3});

select T.x from T where T.x > 1;

Result

[
  2,
  3
]

Compound

Absent Field

Absent field reads as null and fails ordering comparison.

SQL

create table T;

insert into T ({});

select * from T where T.x > 0;

Result

[]

See also