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
('[email protected]'),
('[email protected]'),
('[email protected]'),
('[email protected]')
) 
E(email)
WHERE NOT EXISTS
(
    SELECT 1
    FROM person P 
    WHERE E.email = P.emailid
)

 

tested in PostgreSQL

Author: bm on September 1, 2017
Category: postgresql, SQL

Your comment:

Your Name

Comment:




Last articles