Is there an implementation of this query-syntax in TSQL?

Question

In some SQL dialects, you can state (something as):

SELECT * FROM SomeTable WHERE (val1,val2) IN 
  (SELECT val1,val2 FROM SomeOtherTable)

But I don't know how to do that in the TSQL (sql server 2k) I am using.

I am aware of (and using for now) workarounds like using joins or concatenated values, but is there some syntax in TSQL I am overlooking to do just that?

UPDATE : This is valid SQL-99 syntax, that's why I consider a join a workaround, even if it would be more performant. My question is maybe put better as :

Is there an implementation of this syntax in TSQL?

UPDATE2 : I just tested this syntax om Mysql and it works fine there.

Answer 1

Joining would be the way to go here.

Answer 2

IN only works on single column arrays. Since you've explicitly stated that you want to do an IN clause:

SELECT * FROM SomeTable 
WHERE val1 IN (SELECT val1 FROM SomeOtherTable WHERE val2 = SomeTable.val2) 
AND val2 IN (SELECT val2 FROM SomeOtherTable WHERE val1 = SomeTable.val1)

Check out the documentation on it. Since there's a where clause which depended on each row (e.g.-WHERE SomeOtherTable.ID = SomeTable.MyOtherID), it will hose performance, and a join is absolutely the way to go.

Answer 3

And what about two subselects?

Answer 4

There is not a sql server implementation of that syntax. You'll have to do something like this:

SELECT st.*
FROM SomeTable st
  INNER JOIN  
  (
    SELECT val1, val2 
    FROM SomeOtherTable 
    GROUP BY val1, val2
  ) sot ON sot.val1= st.val1 AND sot.val2 = st.val2

Answer 5

SELECT  *
FROM    SomeTable st
WHERE   EXISTS
        (
        SELECT  1
        FROM    SomeOtherTable sot
        WHERE   sot.val1 = st.val1
                AND sot.val2 = st.val2
        )

This is actually what an IN construct is optimized to with any SEMI JOIN method.

As for your question,

Is there an implementation of this syntax in T-SQL?

the answer is no

As documentation says:

… subquery that has a result set of one column. This column must have the same data type as test_expression.

Answer 6

Select SomeTable.*
FROM SomeTable, SomeOtherTable
WHERE SomeTable.Val1 = SomeOtherTable.Val1
    And SomeTable.Val2 = SoemeOtherTable.Val2

You should not consider this a "workaround." This is the most basic and standard way of accomplishing what you want to accomplish.

Edit: It may not be your standard "JOIN" syntax, but it's a habit.