Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Just my 2¢ , and you do not want to use a subquery right –  kabuto178 Feb 6 '13 at 19:23
    
Right. I'd prefer to avoid a subquery, if possible. –  James Feb 6 '13 at 19:38
    
of course, just a question I am still on my way to become a proper DB admin :). –  kabuto178 Feb 6 '13 at 19:42
    
No problem. Just clarifying :) –  James Feb 6 '13 at 19:47

1 Answer 1

up vote 11 down vote accepted

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 
share|improve this answer
    
Perfect! I'd upvote you, but I don't have enough reputation yet. Thanks for the answer! –  James Feb 6 '13 at 19:51
    
You have enough rep now. You can upvote him !!! –  RolandoMySQLDBA Feb 6 '13 at 20:22
    
Oh yea, +1 for you !!! –  RolandoMySQLDBA Feb 6 '13 at 20:23
    
RolandoMySQLDBA: Just did! :) –  James Feb 6 '13 at 20:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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