selecting where two columns are in a set

Question

This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

I want to select data where two columns are in a set of pairs. Thanks!

Answer 1

Is there a construct in SQL that would allow me to do something like the following:

Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses:

SELECT whatever 
FROM t                               --- you missed the FROM
WHERE (col1, col2)                    --- parentheses here
       IN ((val1, val2), (val1, val2), ...) 

If you try it however in a DBMS, you may find that it doesn't work. Because not all DBMS have implemented all the features of the (evolving) SQL standard. (this works in latest versions of Oracle, MySQL and Postgres.)

Other ways that express the same idea:

SELECT whatever 
FROM t 
WHERE (col1, col2) 
       IN ( VALUES (val1, val2), (val1, val2), ...) 

SELECT t.whatever 
FROM t 
  JOIN 
    ( VALUES (val1, val2), (val1, val2), ...) AS x (col1, col2)
      ON (x.col1, x.col2) = (t.col1, t.col2)

Both work in Postgres only (afaik). The last one works in SQL-Server, too, but needs a modification:

SELECT t.whatever 
FROM t 
  JOIN 
    ( VALUES (val1, val2), (val1, val2), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2