2

I am trying to achieve something like this in sql server 2012. But this syntax does not exists. What are the alternatives to it and why does sql server does not support such syntactic sugar?

Which means C1 match to D1, C2 match to D2, Cn match to Dn. When all of the columns match it only will filter out the result.

SELECT * FROM tableA
WHERE
   ([C1], [C2],..., [Cn]) IN (SELECT [D1], [D2],..., [Dn] FROM tableB)
1
  • Have a look at the INTERSECT operator. It more or less meets your requirements. Sep 10, 2019 at 5:59

3 Answers 3

8

Your query is standard SQL. Unfortunately it hasn't been implemented yet in SQL Server. You can express your support for this feature to be implemented: Microsoft Azure Feedback: Add support for ANSI standard row value constructors.

You can use the common method of transforming IN to an EXISTS subquery. Works with just one or more columns:

SELECT * 
FROM tableA AS a
WHERE EXISTS
      ( SELECT 1 
        FROM tableB AS b
        WHERE a.[C1] = b.[D1]
          AND a.[C2] = b.[D2]
          ---
          AND a.[Cn] = b.[Dn]
      ) ; 
1

What you are proposing can be done with an INNER JOIN with multiple conditions...

...
FROM TableA
INNER JOIN TableB ON
    c1 = d1
and c2 = d2
and c3 = d3
1
  • 1
    This is equivalent if and only if there is a unique constraint on TableB (D1, D2,..., Dn). Sep 10, 2019 at 4:54
0

I just ran into the exact same problem. Used to working with Oracle and Teradata I just wrote a query like:

select * from table 
where ID in 
(select ID from table
group by ID
having count(*) > 1)

This works fine.

Then I modified my query into something like:

select * from table 
where (ID, NAME) in 
(select ID, NAME from table
group by ID, NAME
having count(*) > 1)

And this does not work on SQL Server. Which brought me here. People suggesting to rewrite the query to a join. No, that's not what I want.

So I decided not to tell the database that I was interested in multiple columns but fool it into thinking I was requesting only 1 column (sort of, the database won't really be fooled), by concatenating the columns I needed. To prevent false positives, I added a pipe symbol between the columns.

select * from table 
where concat(ID, '|', NAME) in 
(select concat(ID, '|', NAME) from table
group by concat(ID, '|', NAME)
having count(*) > 1)

This works on SQL Server and gives me the exact result I was looking for.

3
  • 2
    You realise, of course, that your approach, unlike rewriting the query as a join, precludes possible use of indexes, thus negatively affecting performance.
    – mustaccio
    May 6, 2022 at 12:52
  • What @mustaccio said. There is also a (small) chance your query to provide wrong results, if the ID is text and not numeric column and it contains your special pipe character. May 6, 2022 at 23:15
  • Another method to write your query - without joins - is using window functions: select * from ( select *, count(*) over (partition by id, name) as dup_count from table) as t where t.dup_cnt > 1 ; May 6, 2022 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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