How to Use SQL Query with Multiple Criteria in Google Sheets

Reviewing Logical Operators

Deep at the heart of computing lies the logical operators. Logical operators mainly serve to connect two or more expressions together especially when using them as criteria for condition operators. There are three basic logical operators: NOT, AND, and OR.

The NOT operator changes the statement to its opposite.
The AND operator becomes true if both the two expressions it combines are true.
The OR operator becomes true if either one of the two expressions it combines is true.

As an example, let us consider this image and a corresponding list of equations. Try to see if your logic matches the list below!

Tesla Roadster car with a mannequin astronaut floating in space. — *Image courtesy of SpaceX.*

The car is red = TRUE
The car is NOT red = FALSE

‍

The car is red AND the car is in outer space = TRUE
The car is blue AND the car is in outer space = FALSE
The car is red AND the car is in California = FALSE
The car is blue AND the car is in California = FALSE

‍

The car is red OR the car is in outer space = TRUE
The car is blue OR the car is in outer space = TRUE
The car is red OR the car is in California = TRUE
The car is blue OR the car is in California = FALSE

As you can see, all of these operators are useful in their own way! More specifically, in this tutorial, we will learn how to use them in making better queries in Google Sheets. For our examples, we have added a table containing U.S. states’ etymology on one sheet.

U.S. state names etymology sheet. Data from Wikipedia article on U.S. state names etymology.

‍

AND: Requiring two or more criteria to be fulfilled

If we wish to place two criteria that the entries must match to, then we use the AND operator. Continuing on our U.S. state etymology query, we wish to find states whose names originated from the English language and are named after kings. We will use the following query:

select A, B, C, D, E, F where D contains 'English' and F contains 'King'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

We get the following result:

Four listed results for U.S. state names that originated from the English language and have the word “King” in its notes: Georgia, Maryland, New York, and North Carolina.

‍

What we did is to search on the column for the language of origin the word “English” and the column for meaning and notes the word “King”. We got four states listed here. A quick read through the meanings and notes will tell us that Maryland should not be included but South Carolina should, thus giving us four U.S. states that are named after English kings: Georgia, New York, North Carolina, and South Carolina.

OR: Only one of the listed criteria must be fulfilled

The OR operator can be used to look for two or more separate keywords along the same column. For our example, we wish to find states whose names are attested before 1800 and originate from the Spanish language. Our query would look like this:

select A, C, D, E, F where C < 1800 and D contains 'Spanish'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

And we will get the following result:

Two listed results for U.S. state names that originated from the Spanish language and whose name was first attested before 1800: Colorado and Florida.

‍

NOT: Selecting those that did not fulfill the criteria

Sometimes we need entries that do not fit the major categories, let’s say the majority of entries are in a single category, and we need to list down all the other entries that do not fit in that single category. We can use the NOT operator.

For our example, we wish to list down all the U.S. states whose names do not originate from English. We can write the query as follows:

select A, C, D where NOT D contains 'English'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

And our result will look like this:

Listed results for U.S. state names that did not originate from the English language. The list goes beyond what is shown in the image.

Combining the operators

Sometimes we need entries that do not fit the major categories; let’s say the majority of our sales come from three cities, and the rest are from the small towns in the area. It will be easier for us to filter out the three cities instead of placing one-by-one the numerous small towns in the area. For this, we can use a combination of NOT and AND operators.

For our example, we wish to list down the state names that did not originate from English, Spanish, or French. We write the query as follows:

select A, C, D, E, F where NOT D contains 'Spanish' AND NOT D contains 'English' AND NOT D contains 'French'

With this, we'll have to place it in Google Sheets' QUERY function with the format:

=query([range],"[SQL query]'")

And we get the following result:

Image: U.S. states whose names did not originate from Spanish, English, nor French. The ones shown on the screen, in alphabetical order, are near the end of the list: Minnesota, Nebraska, North Dakota, Oklahoma, Oregon, Pennsylvania, Rhode Island, South Dakota, Tennessee, Virginia, West Virginia, and Wyoming.

Counting out the duplicates, we get 21 states whose name did not originate from Spanish, English, nor French.

There is another way to do this. SQL allows nesting and the use of parentheses to group the criteria together. For the same result as above, we will use a combination of NOT and OR operators. The query can be written this way:‍

select A, C, D where NOT (D contains 'English' OR D contains 'Spanish' OR D contains 'French')‍

What used to be the AND operators were changed to OR operators grouped together inside the parenthesis. Google Sheets will interpret this portion of code by listing down rows where the listed info in Column D does not include English, Spanish, or French.
‍

Further refining your queries

This sounds like an easy job, but that’s because we are dealing with a small number of entries. We still need to spend time reading them one-by-one to refine our results. We may have missed out on entries either due to how they were encoded or that we chose the wrong queries. Instead of sweating it out, all to improve our business, consider trying Lido. With a few clicks on your laptop, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!