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.
As an example, let us consider this image and a corresponding list of equations. Try to see if your logic matches the list below!
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.
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:
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.
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:
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:
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:
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.
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!
