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...

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
– Tim Schmelter, Commented Apr 11, 2013 at 6:58
N.B: (a, b) is a called a "row value expression", or a tuple. This might help with googling. — Lukas Eder
– Lukas Eder, Commented Apr 11, 2013 at 7:01

Lukas Eder · Accepted Answer · 2013-04-11 07:18:55Z

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, 2013 at 7:18

answered Apr 11, 2013 at 7:04

Lukas Eder

223k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

Here's the Connect issue if you feel the urge to vote on it.

Lukas Eder Over a year ago

@Damien_The_Unbeliever: Thanks for the link. I will!

TechDo · Accepted Answer · 2013-04-11 07:49:59Z

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, 2013 at 7:49

answered Apr 11, 2013 at 6:56

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

4 Comments

Tim Schmelter Over a year ago

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. sqlfiddle.com/#!3/24f87/5/1

TechDo Over a year ago

Yes, my answer gives only 1 record. In oracle also output is one record(as he mentioned).

Lukas Eder Over a year ago

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)

TechDo Over a year ago

@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?

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related