SQL Tutorials
Understanding FROM
Understanding JOIN
Understanding WHERE
Using dates in SQL
Using strings in SQL
SQL Functions
CASE
COUNT
BigQuery Tutorials
Arrays Explained
Data Types
Dates and Times
String Functions Explained
Window Functions Explained
BigQuery Functions
ARRAY_AGG
CASE
CAST
COALESCE
CONCAT
COUNT [DISTINCT]
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIMESTAMP
DATE_DIFF
DATE_TRUNC
DATETIME_DIFF
DATETIME_TRUNC
EXTRACT
IF
MEDIAN
SUBSTR
TIME_DIFF
TIME_TRUNC
TIMESTAMP_DIFF
TIMESTAMP_TRUNC
UNNEST
Snowflake Tutorials
Histograms
Linear Regression
Moving averages
Pivot Tables
Running Totals
Summary Statistics
Window Functions
SQL Server Tutorials
Data Types
Dates and Times
SQL Resources/SQL/Understanding WHERE

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.

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
numbers
3
4
5

Equality

with my_table as (select * from unnest([1,2,3,4,5]) as numbers)
select numbers from my_table
where
  numbers = 2
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
numbers
2
3
4

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
numbers
NULL
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)
numbers
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
numbers
2
3
4

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)
numbers
5

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
strings
ab
aa
bb

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)
numbers
3
4
5

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'
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'
dates
2020-01-06
2020-01-07
2020-01-08
2020-01-09
2020-01-10

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'
dates
2020-01-02
2020-01-03
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
dates
2020-01-05

Become a SQL Expert
Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.
Email
Title