Understanding WHERE
The WHERE
clause is one of the fundamental building blocks of a SQL SELECT
statement. In this article we'll explain how it works.
-- Generic SQL SELECT statement
select
-- (some columns)
from
-- (some tables)
where
-- (A 'predicate' expression to be used as a filter)
group by
-- (some columns)
The WHERE
clause is formed of the reserved 'where' keyword followed by what is known as a predicate expression - simply an expression which returns true
or false
.
If, for a given row, the predicate returns true
, that row will be included in the output of the query.
If the predicate returns false
, that row will be excluded.
Let's look at some examples.
In this article the code snippets are written in the Google BigQuery Standard SQL syntax.
Simple WHERE
expressions
In these examples, the predicate expression in the WHERE
clause is a simple boolean expression - an expression that returns true
or false
.
Inequality
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
numbers > 2 -- This is the predicate expression
Equality
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
numbers = 2
IS NOT NULL
with my_table as (select * from unnest([null,2,3,4,null]) as numbers)
select numbers from my_table
where
numbers is not null
IS NULL
with my_table as (select * from unnest([null,2,3,4,null]) as numbers)
select numbers from my_table
where
numbers is null
IN
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
numbers in (1,2)
BETWEEN
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
numbers between 2 and 4 -- Note - these limits are inclusive
Compound WHERE
expressions
In the following examples, the WHERE
clause contains more complex predicate expressions, using the building blocks of the AND
and OR
logical operators.
AND / OR
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
-- Use brackets to control precedence
numbers > 2 and (numbers < 3 or numbers > 4)
Functions
with my_table as (select * from unnest(['a', 'b', 'ab', 'aa', 'bb', 'cc']) as strings)
select strings from my_table
where
-- Predicate expressions can contain functions
(strings like 'a%' or strings like 'b%') and length(strings) = 2
SELECT statements
with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
-- Predicate expressions can contain other select statements
numbers > (select 1+1)
WHERE
expressions on dates
When using dates and times, all of the same rules apply. The predicate expression in the WHERE
clause must still return true
or false
, but you are free to use functions and compound logical expressions.
Equality
with my_table as (
select * from unnest(generate_date_array('2020-01-01', '2020-01-10')) as dates
)
select dates from my_table
where
dates = '2020-01-01'
Inequality
with my_table as (
select * from unnest(generate_date_array('2020-01-01', '2020-01-10')) as dates
)
select dates from my_table
where
dates > '2020-01-05'
BETWEEN
with my_table as (
select * from unnest(generate_date_array('2020-01-01', '2020-01-10')) as dates
)
select dates from my_table
where
-- Note - between is inclusive
dates between '2020-01-02' and '2020-01-04'
AND / OR
with my_table as (
select * from unnest(generate_date_array('2020-01-01', '2020-01-10')) as dates
)
select dates from my_table
where
extract(day from dates) = 5 and extract(month from dates) = 1