Find ID's not match a list-CodePudding

I cannot say I am so experienced with SQL. Here is my question. A table TripEvent have millions of rows. It contains a column Bold_ID that is indexed and unique.

So I can have this query

select bold_id from TripEvent where bold_id in (354469477, 354469536, 354469500, 987359)

Result is

354469477
354469536
354469500

as those exists. But I want to reverse it. How can I get a list if id's that don't exists ? In this case it should return one row

I cannot use NOT in query as that would return all rows in table not match my list.

CodePudding user response：

One way is like this:

SELECT DS.*
FROM
(
    VALUES (354469477)
          ,(354469536)
          ,(354469500)
          ,(987359)
) DS (bold_id)
LEFT JOIN TripEvent TE
    ON DS.[bold_id] = TE.[bold_id]
WHERE TE.[bold_id] IS NULL;

Of course, there are diff ways to populated the DS. I will recommend to populate the search IDs in a temporary table.

CodePudding user response：

Since you are using a SQL Server DB, you can use EXCEPT:

SELECT bold_id FROM
(
  SELECT 354469477 AS bold_id
  UNION ALL
  SELECT 354469536
  UNION ALL
  SELECT 354469500
  UNION ALL
  SELECT 987359
) listofValues 
EXCEPT 
SELECT  bold_id
FROM  TripEvent;

OR:

SELECT bold_id FROM
(
  VALUES (354469477),
       (354469536),
       (354469500),
       (987359)
) listofValues(bold_id)
EXCEPT 
SELECT  bold_id
FROM  TripEvent;

Have a look in the documentation

CodePudding user response：

Found this query on another places. Seems to work. Thanks for the good response :)

  SELECT *
  from (values (354469477),(354469536),(354469500),(987359)) as v(id)
  WHERE NOT EXISTS (SELECT BOLD_ID FROM TripEvent WHERE TripEvent.BOLD_ID = v.id)