I could not find the SQL IN logical operator in Google Sheets Query. Is it available or not? If not, is there any alternative available?
In Google Sheets Query, IN as well as NO IN logical operators are not available. But there is an alternative.
If you know the purpose of SQL IN, then you can look for an alternative. So let me explain the purpose of the SQL IN logical operator.
Purpose:
To match multiple values in a list, we can use the IN operator in the SQL WHERE (I am not familiar with SQL, but I am accustomed to using Query in Sheets).
Actually, the SQL IN logical operator is equivalent to using multiple OR conditions. For multiple OR, we can use Matches operator in Query.
This post describes how to use MATCHES operator as the SQL IN and NOT IN alternatives in Google Sheets Query.
If you want to learn Matches, which is a regular expression match, in full detail, please check my guide Matches Regular Expression Match in Google Sheets Query.
SQL IN Operator Alternative in Google Sheets Query
I have a table of products (A1:C) with the first column contains product names, the second and third columns contain the unit price and the stock availability respectively.
I want to find the products where the product names match the names in a provided list in another column, E.
We can call this column E as the criteria column.
If you don’t want to specify column E, then it’s quite easy to code the Query. I mean you can just list the names to find within Query.
Use the Matches in Query Where and enter the product names to find. The product names must be separated by |.
The formula for cell G1.
=query(A1:C,"Select * where A matches 'Apple|Sweet Melon|Avocado'")But to find the products using product names in another column (here column E), use the formula as below.
=query(A1:C,"Select * where A matches '"&TEXTJOIN("|",true,E1:E)&"'")Textjoin with Matches – Explanation
The TEXTJOIN will combine the criteria and place the pipe symbol in between. Any doubt?
Key this TEXTJOIN formula in a cell and see what it returns.
=TEXTJOIN("|",true,E1:E)As per the above example, it would definitely return the string Apple|Sweet Melon|Avocado.
The above TEXTJOIN must be placed between '"& and this &"' as a common practice in the use of text criterion as cell reference in Query like '"&E1"'. See the example formulas below.
=query(A1:C,"Select * where A matches '"&E1&"'")
which is equal to
=query(A1:C,"Select * where A matches 'Apple'")For more details of proper criteria use in Query, please read Examples to the Use of Literals in Query in Google Sheets.
This way you can use Matches as an alternative to SQL IN logical operator in Google Sheets Query.
SQL NOT IN Alternative
When you want to find products that are not on a list, you can depend on NOT IN alternative. I mean to find the products excluding the one listed in a separate column.
If you have followed the above examples, here things will be easy for you. Simply put a NOT (NOT logical operator) before the column to search.
=query(A1:C,"Select * where not A matches '"&TEXTJOIN("|",true,E1:E)&"'")You must prefix the NOT operator to the column identifier, not a suffix.
Hi Prashanth,
It was helpful, but I may have run into an issue. Am I correct to assume the “matches” operator is case-insensitive, like the match function?
The MATCHES substring match is case-sensitive. To make it case-insensitive, use LOWER or UPPER functions.
Example:
=query(A1:C,"Select * where lower(A) matches'apple|sweet melon|avocado'")
Hello,
I got a result I want to see, but in
TEXTJOIN("|",true,E1:E), what’s the meaning of “|”?It’s confusing how it works in the expression.
Hi, JunKim,
In the MATCHES regular expression operator in Query, the “|” (pipe/vertical bar) symbol is to match any of two or more options similar to the OR Spreadsheet function.
‘Brazil|Japan’ – To match Brazil or Japan (choice of alternatives)
Very useful post. I use to stumble at this issue many a time. I had to look for workarounds by going for a helper column. But this really sleek.
Thanks, Sanjay for your time to post your feedback!
Thank you for the great article. I try the same query with “contains” for partial matches, but result = #NA
Hi Zaur,
Glad to hear that you liked it.
Regarding multiple ‘contains’ partial match please read this guide.
Multiple CONTAINS in WHERE Clause in Google Sheets Query
Best,