Delete with LEFT OUTER JOIN in PostgreSQL

Question

I'm trying to remove all rows from one table that don't have the corresponding key in a second table. So for example in my devices table I have id values of 1 and 2. In my registrations table I have deviceid of 1.

Because 2 doesn't exist in the registration table, I want to remove that row from the devices table, but leave id 1.

In Microsoft SQL I'd write something like this:

DELETE d
FROM devices d
LEFT OUTER JOIN registrations r on d.id = r.deviceid
WHERE r.deviceid IS NULL

What's the equivalent in PostgreSQL? I tried the below, but it deleted all rows from devices:

DELETE FROM devices
USING devices AS d
LEFT OUTER JOIN registrations AS r ON d."id" = r.deviceid
WHERE r.deviceid IS NULL

Answer 1

I would use a NOT EXISTS condition:

delete from devices d
where not exists (select * 
                  from registrations r
                  where d.id = r.deviceid);