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.

I'd like to avoid using a subquery, if possible.

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:

-- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB 
SELECT whatever 
FROM t                               --- you missed the FROM
WHERE (col1, col2)                    --- parentheses here
       IN ((val1a, val2a), (val1b, val2b), ...) ;

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, Postgres, DB2 and HSQLDB (it was not well optimized in MySQL and not use indexes, so it should be avoided there unless they fixed it in 5.7).

See MySQL documentation about IN operator and Postgres documentation about Row constructors. The two* (or more) values in parentheses is called a row-constructor.

Other ways that express the same idea:

-- works in PostgreSQL, DB2
SELECT whatever 
FROM t 
WHERE (col1, col2) 
       IN ( VALUES (val1a, val2a), (val1b, val2b), ...) ;

SELECT t.whatever 
FROM t 
  JOIN 
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON (x.col1, x.col2) = (t.col1, t.col2) ;

Both work in Postgres and DB2 (afaik). The last one can be modified to work in SQL Server, too:

-- works in PostgreSQL, DB2, SQL Server
SELECT t.whatever 
FROM t 
  JOIN 
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

It can also be modified to work everywhere, by placing the values into a (temporary or permanent) table first:

-- works everywhere
CREATE TABLE values_x
( col1  ...,
  col2  ...) ;

-- use appropriate for the DBMS syntax here
INSERT INTO values_x (col1, col2)
VALUES (val1a, val2a), (val1b, val2b), ... ;

SELECT t.whatever 
FROM t 
  JOIN values_x  x 
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

DROP TABLE values_x ;

And there is always the long way or converting the IN to a long expression with OR that should work everywhere:

-- works in all SQL DBMS
SELECT whatever 
FROM t  
WHERE col1 = val1a AND col2 = val2a
   OR col1 = val1b AND col2 = val2b
   ---
   ;

*: It can actually be just one value, with ROW(v), see Postgres docs.

Answer 2

SELECT * 
FROM   dbo.Table1 A
WHERE  (CAST(Column1 AS VARCHAR(max)) + '-' + CAST(Column2 AS varchar(max)))
NOT IN (SELECT (CAST(Column1 AS VARCHAR(max)) 
                + '-' 
                + CAST(Column2 AS varchar(max))) 
        FROM Table2)