T-SQL equivalent to oracle sql where clause with multiple columns

Question

Let's look for example Oracle SQL, which works perfectly:

Sample data:

SQL> create table test (a number, b number);
SQL> insert into test values(1, 1);
SQL> insert into test values(1, 2);
SQL> insert into test values(1, 3);
SQL> insert into test values(1, 4);
SQL> insert into test values(1, 5);
SQL> insert into test values(2, 1);
SQL> insert into test values(2, 2);
SQL> insert into test values(2, 3);
SQL> insert into test values(2, 4);
SQL> insert into test values(2, 5);
SQL> insert into test values(4, 1);

SQL> select * from test;

         A          B
---------- ----------
         1          1
         1          2
         1          3
         1          4
         1          5
         2          1
         2          2
         2          3
         2          4
         2          5
         4          1

Query:

SQL> select * from test where (a, b) in (select 1, 4 from dual);

         A          B
---------- ----------
         1          4

Here's the sql-fiddle: http://www.sqlfiddle.com/#!4/8375e/3/0

Simple question: is there any equivalent in MS SQL of above "where (a, b)" clause? I've been looking around in google, MS Docs and nothing so far...

http://stackoverflow.com/questions/4452539/sql-server-in-clause-with-multiple-fields — Habib, Apr 11 '13 at 06:58
How does it work in Oracle? Is it the same as `select * from test where a = 1 and b = 4;`? What is the benefit then? — Tim Schmelter, Apr 11 '13 at 06:58
N.B: `(a, b)` is a called a "row value expression", or a tuple. This might help with googling. — Lukas Eder, Apr 11 '13 at 07:01
Related question: http://stackoverflow.com/questions/1474964/using-tuples-in-sql-in-clause — Lukas Eder, Apr 12 '13 at 14:01

Lukas Eder · Answer 1 · 2013-04-11T07:18:55.440

5

While SQL Server has a Table Value Constructor that can be used for some use-cases, SQL Server doesn't support SQL standard row value expressions and predicates derived from row value expressions for general use (yet). You will have to resort to semi-joining your subquery using an equivalent EXISTS clause:

This:

select * from test where (a, b) in (select 1, 4 from dual);

Is equivalent to this (see SQLFiddle demo):

select * from test where exists (
  select * from (
    select 1, 4 -- Replace with "real" subselect
  ) t(a, b)
  where test.a = t.a and test.b = t.b
)

Or, a bit more generically, by using a common table expression (See SQLFiddle demo):

with t(a, b) as (
  select 1, 4 -- Replace with "real" subselect
)
select * from test where exists (
  select * from t
  where test.a = t.a and test.b = t.b
)

edited Apr 11 '13 at 07:18

answered Apr 11 '13 at 07:04

Lukas Eder

211,314
129
689
1,509

Here's the [Connect issue](http://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors) if you feel the urge to vote on it. – Damien_The_Unbeliever Apr 11 '13 at 07:46
@Damien_The_Unbeliever: Thanks for the link. I will! – Lukas Eder Apr 11 '13 at 07:49

TechDo · Answer 2 · 2013-04-11T07:49:59.617

0

How about below query, which supports in sql server; and I guess a=1 and b=4 gives the same result in sql server equivalent to oracle query.:

select 
    * 
from 
    test 
where 
    a=1 and 
    b=4;

edited Apr 11 '13 at 07:49

answered Apr 11 '13 at 06:56

TechDo

18,398
3
51
64

1

Is this the same as the oracle query(i don't know)? It'll give one record as the other answers but if you insert another with `a=4,b=1` you'll get two records instead of one. http://www.sqlfiddle.com/#!3/24f87/5/1 – Tim Schmelter Apr 11 '13 at 07:24
Yes, my answer gives only 1 record. In oracle also output is one record(as he mentioned). – TechDo Apr 11 '13 at 07:27
Yes, this query isn't correct. According to the SQL standard, `(a, b) = (1, 4)` is equivalent to `(a = 1) AND (b = 4)`. This accounts for the `IN` predicate and subselects just as well. Your predicate is equivalent to `(1 = a OR 1 = b) AND (4 = a OR 4 = b)` – Lukas Eder Apr 11 '13 at 07:37
@TimSchmelter You are correct; It's giving 2 records. I didn't checked with entry (4, 1) actually. So `select * from test where a=1 and b=4` will the result no? – TechDo Apr 11 '13 at 07:37

T-SQL equivalent to oracle sql where clause with multiple columns

2 Answers2