EXCEPT clause in SOQL

I would appreciate a little help.

How do I write EXCEPT clause in Salesforce SQL?

I would like to have query which excludes rows based on a few conditions from the same database.

Like this:

SELECT Email_address
FROM Database_1
EXCEPT
SELECT Email_address
FROM Database_1
WHERE Brand_country = 'xyz' AND Address_country = 'abc'

 
SELECT Email_addressFROM Database_1EXCEPTSELECT Email_addressFROM Database_1WHERE Brand_country = 'xyz' AND Address_country = 'abc'​

Is there another keyword?

Or maybe is there a better/easier way to do this?

Thanks in advance!

Answer

You can use aggregation:

SELECT Email_address
FROM Database_1
GROUP BY Email_address
HAVING SUM(CASE WHEN Brand_country = 'xyz' AND Address_country = 'abc' THEN 1 ELSE 0 END) = 0;

 
SELECT Email_addressFROM Database_1GROUP BY Email_addressHAVING SUM(CASE WHEN Brand_country = 'xyz' AND Address_country = 'abc' THEN 1 ELSE 0 END) = 0;​

Advertisement

Answer