I've got two tables:
login_log :: ip | etc.
ip_location :: ip | location | hostname | etc.
WHERE NOT EXIST (SELECT ip_location.ip
WHERE login_log.ip = ip_location.ip)
ERROR: syntax error at or near "SELECT"
LINE 3: WHERE NOT EXIST (SELECT ip_location.ip`
There are basically 4 techniques for this task, all of them standard SQL.
Often, this is fastest in Postgres.
SELECT ip FROM login_log l WHERE NOT EXISTS ( SELECT 1 -- it's mostly irrelevant what you put here FROM ip_location i WHERE l.ip = i.ip );
Sometimes this is fastest. Often shortest.
SELECT l.ip FROM login_log l LEFT JOIN ip_location i USING (ip) -- short for: ON i.ip = l.ip WHERE i.ip IS NULL;
Short. Not as easily integrated in more complex queries.
SELECT ip FROM login_log EXCEPT ALL -- ALL, to keep duplicate rows and make it faster SELECT ip FROM ip_location;
Note that (per documentation):
duplicates are eliminated unless
EXCEPT ALLis used.
Typically, you'll want the
ALL keyword. If you don't care, still use it because it makes the query faster.
Only good for small sets without NULL values or if you know to handle NULL properly! I would not use it for this purpose. Performance deteriorates with bigger tables.
SELECT ip FROM login_log WHERE ip NOT IN ( SELECT DISTINCT ip -- DISTINCT is optional FROM ip_location );
NOT IN carries a "trap" for
NULL values on either side:
Similar question on dba.SE targeted at MySQL: