WHERE and HAVING in BigQuery

• WHERE
• HAVING

WHERE

The WHERE clause filters the results of the FROM clause. It filters rows based on a specified condition or conditions.

Single-Condition

For example, I want to find all products whose price is under 4.00.

SELECT Name, Price  FROM `instruction-415216.examples.ivys_shop` 
WHERE Price  <  5.00    #  this is a condition

It returned 31 results. Here we only show 5 records.

Row	Name	Price
1	Kiwi	0.85
2	Kiwi	2.7
3	Peach	1.02
4	Peach	0.72
5	Banana	1.24
	…	…

Multi-Condition : AND

For example, I want to find all products whose price is under 3.00 and has been sold at least 20 pieces.

SELECT Name,Price,Sold  FROM `instruction-415216.examples.ivys_shop` 
WHERE Price  <  3.00 AND  Sold >= 20    # multiple conditions

It returned 14 results and here we only show 5 records.

Row	Name	Price	Sold
1	Kiwi	0.85	57
2	Kiwi	2.7	22
3	Peach	1.02	71
4	Peach	0.72	44
5	Pineapple	2.57	24

Multi-Condition : OR

Another example, what if I want to find all products whose name is “Peach” or “Pineapple”?

SELECT Name,  Price,  Sold  FROM `instruction-415216.examples.ivys_shop` 
WHERE Name  =  "Peach" OR Name  =  "Pineapple" 
# It can be written in this way:  WHERE NAME IN ("Peach","Pineapple")

Row	Name	Price	Sold
1	Peach	1.02	71
2	Peach	4.33	7
3	Peach	4.1	25
4	Peach	0.72	44
5	Pineapple	8.84	1
6	Pineapple	7.86	3
7	Pineapple	5.31	2
8	Pineapple	2.57	24
9	Pineapple	9.85	52

HAVING

The HAVING clause filters the results produced by GROUP BY or aggregation. GROUP BY or aggregation must be present in the query. 

For example, I want to find all vegetables whose price is above 8.00.

Notice, the WHERE condition is before GROUP BY and the HAVING condition is after GROUP BY

SELECT Name,Price FROM `instruction-415216.examples.ivys_shop` 
WHERE  Category  = 'Vegetable'  # the  WHERE  condition  is before GROUP BY
GROUP BY Name, Price
HAVING Price  > 8              # the having  condition  is after  GROUP BY

Row	Name	Price
1	Onion	9.07
2	Potato	9.98
3	Potato	8.06
4	Tomato	9.51
5	Broccoli	9.55
6	Broccoli	9.35
7	Cucumber	9.72
8	Zucchini	8.72
9	Zucchini	9.64

The HAVING clause can have multiple conditions, too.

SELECT Name, Price, Sold FROM `instruction-415216.examples.ivys_shop` 
WHERE  Category  = 'Vegetable'
GROUP BY Name, Price, Sold
HAVING Price  > 8 AND Sold  > 30      # multiple condition

Row	Name	Price	Sold
1	Potato	9.98	70
2	Cucumber	9.72	31
3	Zucchini	8.72	35