SQL

SQL getting items not in a list

SQL getting items not in a list

In the bellow example, getting list of person id not exist in the given list

SELECT E.person_id
FROM (VALUES
(661),
(1826),
(2036),
(2567),
(2922),
(3408)
) E(person_id)
WHERE NOT EXISTS
(
    SELECT 1
    FROM persontable P 
    WHERE E.person_id = P.id
)

For strings (varchars)

SELECT E.email
FROM (VALUES
('email1@test.com'),
('email2@test.com'),
('email3@test.com'),
('email4@test.com')
) 
E(email)
WHERE NOT EXISTS
(
    SELECT 1
    FROM person P 
    WHERE E.email = P.emailid
)

 

tested in PostgreSQL

By bm on September 1, 2017 | postgresql, SQL | A comment?
Read more