SQL Server: Select * from x where (a,b) in (select a,b from z)

Question

This does not work in SQL Server:

SELECT ID, SQNCNO
FROM JUID AS MID
WHERE 
     (ID, SQNCNO) IN (SELECT NM.ID, NM.SQNCNO
                      FROM JUNM AS NM
                      WHERE (LAST_NAME LIKE 'TESTCASE%')) 
ORDER BY ID, SQNCNO

It works in DB2 and Oracle. The problem is WHERE (ID, SQNCNO). Can anyone tell me how I can make this SQL work in SQL Server?

SQL Server doesn't currently support tuples with `in`. – Martin Smith Apr 29 '14 at 18:51 — Martin Smith, Apr 29 '14 at 18:51

score 2 · Accepted Answer · answered Apr 29 '14 at 18:42

Exists is created for this manner

SELECT ID, SQNCNO
FROM JUID AS MID
WHERE Exists(SELECT 1 FROM JUNM AS NM
                      WHERE 
                          NM.ID = MID.ID 
                          and NM.SQNCNO = MID.SQNCNO 
                          and (LAST_NAME LIKE 'TESTCASE%')) 
ORDER BY ID, SQNCNO

score -1 · Answer 2 · answered Apr 29 '14 at 18:29

-1

In SQL Server, you have to make two subqueries

SELECT ID, SQNCNO
  FROM JUID AS MID
  WHERE (ID IN (SELECT NM.ID
                   FROM JUNM AS NM
                   WHERE (LAST_NAME LIKE 'TESTCASE%')) 
OR -- AND?
SQNCNO IN (SELECT NM.SQNCNO
                   FROM JUNM AS NM
                   WHERE (LAST_NAME LIKE 'TESTCASE%')) 
  ORDER BY ID, SQNCNO

answered Apr 29 '14 at 18:29

Ortiga

8,455
5
42
71

This isn't equivalent to the query in the OP. The comparison needs to be row wise. – Martin Smith Apr 29 '14 at 18:54

SQL Server: Select * from x where (a,b) in (select a,b from z)

2 Answers2