30

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.

41

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.

  • Where can I find the documentation about WHERE (x, y) IN (a,b)? I am using MySql. Perhaps I don't know what this construct is called. – Robert Rocha Oct 9 '17 at 2:03
  • 1
    @RobertRocha see the links I added. It's called a row constructor: MySQL: IN and Postgres: Row Constructors – ypercubeᵀᴹ Oct 9 '17 at 6:28
  • There's also WHERE EXISTS (SELECT t.col1, t.col2 [FROM DUAL] INTERSECT VALUES(val1, val2), (…, …), …). – Andriy M Jan 26 '18 at 13:46
-4
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)
  • 2
    That is not going to work reliably – a_horse_with_no_name Jan 25 '18 at 10:03
  • 2
    Yes. Besides inefficiency, it won't be able to differentiate between 'a-b', 'c' and 'a', 'b-c'. And it will fail miserably for any type that can't be converted to varchar(max). – ypercubeᵀᴹ Jan 25 '18 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.