How to Use SQL Queries with Multiple Criteria in Google Sheets

GiGiKing·March 14, 2024·0 comments

Using SQL queries in Google Sheets can help you filter and organize your data more effectively. In this article, we will explore how to use SQL queries with multiple criteria to achieve more precise results.

Understanding Logical Operators

Logical operators are essential elements in computer science. They are mainly used to connect two or more expressions, especially when used as criteria for conditional operators. There are three basic logical operators: NOT, AND, and OR.

  • The NOT operator negates a statement.
  • The AND operator is true if both expressions it combines are true.
  • The OR operator is true if at least one of the expressions it combines is true.

To better understand these operators, let’s look at the following example:

The car is red = TRUE
The car is not red = FALSE
The car is red AND the car is in space = TRUE
The car is blue AND the car is in 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 space = TRUE
The car is blue OR the car is in 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 these operators have their uses! In this tutorial, we will learn how to use them to perform more precise queries in Google Sheets.

AND: Requiring Two or More Criteria to Be Met

If you want to filter data based on two or more criteria, you need to use the AND operator. Let’s take an example of a query about the etymology of American states. We want to find states whose names are of English origin and are named after kings. Here’s the corresponding query:

SELECT A, B, C, D, E, F WHERE D CONTAINS 'English' AND F CONTAINS 'King'

To execute this query in Google Sheets, you need to use the QUERY function with the following format:

QUERY(range, query, headers)

Here’s the result:

Image

By searching for the word “English” in the original language column and the word “King” in the meaning column, we get a list of four states. A quick read of the meanings and notes tells us that Maryland should not be included, but South Carolina should be. Thus, we get four American states named after English kings: Georgia, New York, North Carolina, and South Carolina.

OR: One of the Listed Criteria Must Be Met

The OR operator can be used to search for multiple distinct keywords in the same column. Let’s take an example of searching for states whose names are attested before 1800 and are of Spanish origin. Here’s the corresponding query:

SELECT A, C, D, E, F WHERE C < 1800 AND D CONTAINS 'Spanish'

Here’s the result:

Image

NOT: Selecting those that Do Not Meet the Criteria

Sometimes, we need data that doesn’t fall under the main categories. For example, if the majority of data belongs to one category, and we want to list all the data that doesn’t belong to that category, we can use the NOT operator.

Let’s take the example of searching for American states whose names are not of English origin. Here’s the corresponding query:

SELECT A, C, D WHERE NOT D CONTAINS 'English'

Here’s the result:

Image

Combining Operators

Sometimes, it’s easier to filter data by excluding certain categories rather than including a large number of categories. In this case, we can combine the NOT and AND operators.

Let’s take the example of searching for states whose names are not of English, Spanish, or French origin. Here’s the corresponding query:

SELECT A, C, D, E, F WHERE NOT D CONTAINS 'Spanish' AND NOT D CONTAINS 'English' AND NOT D CONTAINS 'French'

Here’s the result:

Image

By excluding duplicates, we get 21 states whose names do not come from Spanish, English, or French.

There is another way to achieve the same result. SQL allows the use of parentheses to group criteria together. To get the same result, we can use a combination of the NOT and OR operators:

SELECT A, C, D WHERE NOT (D CONTAINS 'English' OR D CONTAINS 'Spanish' OR D CONTAINS 'French')

This query filters rows where the information listed in column D does not include English, Spanish, or French.

Fine-Tune Your Queries

While SQL queries are useful for filtering data, they can be tedious to use and often require careful reading of the results to fine-tune them. To make this task easier and obtain relevant results without having to code complex formulas, I recommend trying Lido. With just a few clicks, you can get all the relevant data without having to access SQL databases on your e-commerce platforms.

For more information, visit Crawlan.com and discover how to simplify your data queries!

Don’t forget to share your tips and experiences in the comments. See you soon!

GiGiKing

Related posts