SQL: Work arounds for multi-column IN queries in T-SQL

We all use IN when writing queries:

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size        Color 
370ml   Blue
370ml   Red
220ml   Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

I wish T-SQL had that option but it doesn't. It would be particularly useful when those values in the IN clause are coming from a sub-query.

Using CONCAT

One option is to use CONCAT to concatenate the strings. It takes a list of values, ignores NULL values, implicitly casts all values to strings, and concatenates the results.

That's the solution that looks closer to what you were trying to achieve, but it does more work than needed.

Using VALUES and a join

The other way is to just create a table expression using row constructors and to then join to it:

Using EXISTS

Tiago Rente reminded me in the comments on LinkedIn that of course we could have used EXISTS rather than an INNER JOIN. I actually prefer the EXISTS as it keeps the filtering in a single predicate. It could look like this:

Using a CTE for Clarity

Anthony Duguid also mentioned in the LinkedIn comments that he'd like a CTE in there, to allow for a clearer name for the subquery. In fact, I think I'd like the combination of a CTE and EXISTS the best:

 

One thought on “SQL: Work arounds for multi-column IN queries in T-SQL”

  1. 🙃 sense a philosophical T-SQL differential union view projection lesson coded in the white space between the technical code in this post

Leave a Reply

Your email address will not be published.