SQL to search and delete using multiple key columns

Question

A bit of a bad name maybe, but I'm trying to explain what I mean.

I am using SQL like this to create a list

SELECT id 
FROM parcel 
WHERE id IN (113715, 113824, 113855, 113954, 114010, 114116, 114272, 114329)

where IDis the column in the package table that is quarantined to be unique, very simple.

But some tables use many columns to be unique.

SELECT id1, id2 
FROM trip 
WHERE id1, id2 IN ((113715, 113824), (113855, 113954), (114010, 114116),(114272, 114329))

Last SQL obviously does not work.

I want to select the rows where

id1 = 113715 and id2=113824 or
id1 = 113855 and id2=113954 or
id1 = 114010 and id2=114116 or
id1 = 114272 and id2=114329

In fact, the generated SQL can contain 500-1000 identifiers.

Which SQL should I use?

EDIT

This is generated SQL. The identifier comes from another database on another server, so JOIN, for example, is not possible.

Answer 1

SELECT id1, id2 FROM trip 
INTERSECT
select id1, id2 from (values (113715,113824),(113855,113954),(114010,114116),(114272,114329)) a(id1,id2)

INTERSECT

Answer 2

:

SELECT id1, id2 
FROM trip 
INNER JOIN (VALUES (113715,113824),(113855,113954),(114010,114116),(114272,114329)) V(a, b)
ON id1 = a 
AND id2 = b

Answer 3

Can you use created identifiers like this?

;WITH GenerateIds AS
(
  SELECT * FROM (VALUES
   -- Paste the Id list below
   (113715,113824),(113855,113954),(114010,114116),(114272,114329)
  ) t(id1, id2)
)
-- Use inner join
SELECT id1, id2 FROM trip INNER JOIN GenerateIds g ON 
   trip.id1 = g.id1 AND trip.id2 = g.id2

Answer 4

Unfortunately, SQL Server does not support tuples in IN, for example

SELECT id1, id2 
FROM trip 
WHERE (id1,id2) in ((113715,113824),(113855,113954),(114010,114116),(114272,114329));

which is valid in some other DBMSs.

So you can either follow your own suggestion

SELECT id1, id2
FROM trip 
WHERE (id1 = 113715 and id2 = 113824)
   OR (id1 = 113855 and id2 = 113954)
   OR (id1 = 114010 and id2 = 114116)
   ...;

Or use a temporary table, cte or the like. The easiest way would be to join the values ​​clause:

SELECT trip.id1, trip.id2
FROM trip 
JOIN (VALUES (113715,113824),(113855,113954),(114010,114116),(114272,114329))
  AS src(id1, id2) ON src.id1 = trip.id1 AND src.id2 = trip.id2;

Answer 5

Another simple request:

SELECT id1, id2 
FROM trip 
WHERE 1 = 1
AND ((id1 = 113715 and id2=113824) 
    OR (id1 = 113855 and id2=113954) 
    OR (id1 = 114010 and id2=114116) OR (id1 = 114272 and id2=114329))