2: Google Sheets Query WHERE
Users apply WHERE when they need to pull specific rows from the columns, they have already identified in the SELECT clause, which satisfies one or more conditions.
To compare values across rows, one needs to be aware of these basic logical operators that accompany the WHERE clause.
Google Sheets Query WHERE basic operators example
In my case, the ready-to-use formula will read:
=query('data from Airtable'!A:L,"select C, E, I WHERE I>=40")
'data from Airtable'!A:L – the data range to query on
"select C, E, I WHERE I>=40" – pull the data from columns C, E, I, where the value in column I (total price) is more than or equals 40.
Note: if you want to say that the cell is empty or its contents equal 0 – use the is null operator, and if you want to select rows that are not empty, then enter is not null.
One can combine several conditions using and, or, and not as part of the WHERE clause in the query like this:
=query('data from Airtable'!A:L,"select C, E, I WHERE I>=40 and not E='Denver sandwich'")
'data from Airtable'!A:L – the data range to query on
"select C, E, I WHERE I>=40 and not E='Denver sandwich'" – pull the data from columns C, E, I, where the value in column I (total price) is more than or equals 40 and where the string in column E (product) does not include the Denver sandwich.
Google Sheets Query WHERE advanced operators example
Use these advanced comparison operators to run more complex queries:
Here is the sample formula
=query('data from Airtable'!A:L,"select C, E, I WHERE E starts with 'C' and C like 'K%'")
'data from Airtable'!A:L – the data range to query on
"select C, E, I WHERE E starts with 'C' and C like 'K%'" – the string pulls the data from columns C, E, I, where the value in column E (product) starts with the “C” letter and where the string in column C (customer name) starts with the “K” letter.
Google Sheets Query Matches example
The matches operator helps in advanced pattern matching using regular expressions and use complex search criteria within queries.
=query('data from Airtable'!A:L,"select C, E, I WHERE E matches 'Steak sandwich'")
'data from Airtable'!A:I – the data range to query on
"SELECT C, E, I WHERE E MATCHES 'Steak sandwich'" – filters the dataset to return only the rows where column E (product) exactly matches the phrase “Steak sandwich”. It then displays the C (customer name), E (product), and I (total price) columns for those filtered rows.
