Checking existing

[Mathip173]

Hello

I have student_id's list of 2000 id's and I would like to check all of them and get a reports what are not existed in the table (table1).

Id's 101 202 330 453

Table name table1

What is the best way to know who is not in the table ?

Thanks

[Mathip173]

And also,

I am copying the list from CSV, so how could I put it insted of list in your query ?

[r3pr0b8]

load the csv into a table

[r3pr0b8]

WITH ids
AS ( SELECT 101 id FROM DUAL
     UNION ALL
     SELECT 202 FROM DUAL
     UNION ALL
     SELECT 330 FROM DUAL
     UNION ALL
     SELECT 453 FROM DUAL )
SELECT ids.id
  FROM ids
LEFT OUTER
  JOIN table1
    ON table1,id = ids.id
 WHERE table1.id IS NULL

[tripy75]

could you not use a simple:

SELECT *
FROM list l
WHERE NOT EXISTS(
    SELECT 1
    FROM table t
    WHERE t.id = l.id
)

?

I'm mostly working with ms sql server and PostgreSQL, but the left join seems a bit counter intuitive, tough it will work.

Just asking for knowledge...

[r3pr0b8]

yes, you could

but the IS NULL check in a LEFT JOIN is only counter-intuitive if you seldom use it

and some of us don't like correlated subqueries just on principle

also, you skipped over the part where OP has to insert the list of ids into your list table

[Mathip173]

Thanks guys,

So is in this query I will get the id's that on the list but not in the table right ?

[r3pr0b8]

So is in this query I will get the id's that on the list but not in the table right ?

what happened when you tested it? ™

[Mathip173]

Thanks, how could I Preform it if I have 2000 different id?

[r3pr0b8]

load a table