You may have seen the use of WHERE 1=1 in Google Sheets Query formulas. Usually, an IF logical test comes immediately after it.

Do you know the purpose of using WHERE 1=1 in the Query function in Google Sheets?

We use the above statement/string in the Google Visualisation API Query Language query to manipulate data in a specific way.

Syntax of the QUERY function: QUERY(data, query, [headers])

WHERE is one of the clauses in the query which is optional to use.

The purpose of the WHERE clause is to return only rows that match specific conditions.

If omitted, the formula would return all the rows unless there is no LIMIT clause present.

You May Like: What is the Correct Clause Order in Google Sheets Query?

But we can specify the WHERE clause and return all the rows (if the LIMIT clause is not present) using the said string/statement in Google Sheets Query.

The purpose of using WHERE 1=1 in Google Sheets Query is to specify a second condition without defining the first condition, or, in other words, we can say the first condition is equal to all rows.

A Real-Life Example (Explanation Follows):

Condition # 1: “All Fruits” (WHERE 1=1). It returns all rows and has no relation with the values in A1:A.

The (Real) Condition # 2: The criteria specified in cell B1 other than “All Fruits.” It’s related to the values in A1:A.

Example to WHERE 1=1 Google Sheets Query

Let’s consider fruit names in column A (cell range A1:A) as the data to use.

The following formula will return all the rows in this column.

Formula_1:

=QUERY({A1:A},"SELECT Col1")

If we want to use the WHERE clause and return all the rows, we can use the below formula.

Formula_2:

=QUERY({A1:A},"SELECT Col1 WHERE 1=1")

That means Formula_1 = Formula_2.

Now I want to return all the rows if B1=”All Fruits.”

Here comes the real-life use of the statement WHERE 1=1 in Google Sheets Query.

Formula_3:

=QUERY({A1:A},"SELECT Col1 WHERE 1=1"&if(B1="All Fruits",""))

The IF statement returns blank if B1 is “All Fruits” because we have specified null in the value_if_true part of the statement.

Syntax: IF(logical_expression, value_if_true, value_if_false)

logical_expression – B1="All Fruits"

value_if_true – ""

value_if_false – We have yet to specify this parameter.

That means if B1=”All Fruits” Formula_3 = Formula_2.

Adding a Second Condition (‘All Rows’ is the First Condition)

We have yet to specify any condition in the Query WHERE clause that involves any column in the data.

The “All Fruits” returns all rows in the data. So it’s not a condition that involves any column in the data.

The below formula will return rows matching “Apple” in column 1. That means we have used one condition.

=QUERY({A1:A},"SELECT Col1 WHERE Col1 = 'Apple'")

Note:- When using a string as literal, enclose them within single-quotes or double-quotes.

If we enter Apple in cell B1, specify it as '"&B1&"'.

=QUERY({A1:A},"SELECT Col1 WHERE Col1 = '"&B1&"'")

Below, we will use Col1 = '"&B1&"' as the second condition. The first condition will be WHERE 1=1.

WHERE 1=1 (Real-Life Use)

In the following real-life scenario, we can apply WHERE 1=1 in a Google Sheets Query for filtering data.

B1=”Apple” – return rows containing “Apple.”

B1=”All Fruits” – return all the rows without applying any condition.

To do this, we should use Col1='Apple' (Col1 = '"&B1&"') as the second condition.

The second condition is usually followed by AND, OR, NOT logical operators in Query. Here we will use AND.

So Col1='Apple' becomes AND Col1='Apple' (AND Col1 = '"&B1&"').

Here we can use WHERE 1=1 in Google Sheets Query as the first condition.

In Formula_3, we haven’t specified the value_if_false part. There specify " AND Col1 = '"&B1&"'" (second condition).

=QUERY({A1:A},"SELECT * WHERE 1=1 "&IF(B1="All Fruits",""," AND Col1 = '"&B1&"'"))

If the value in B1=”All Fruits”, the formula becomes =QUERY({A1:A},"SELECT * WHERE 1=1").

If B1=”Orange”, the formula becomes =QUERY({A1:A},"SELECT * WHERE 1=1 AND Col1 = 'Orange'").

I’ve used the above formula in cell D1. Using data validation in cell B1, I’ve controlled the filter.

That’s all. Thanks for the stay. Enjoy!

Related:- Google Sheets: How to Get an All Selection Option in a Drop-down.