Select rows which are not present in other table

Question

I've got two postgresql tables:

table name     column names
-----------    ------------------------
login_log      ip | etc.
ip_location    ip | location | hostname | etc.

I want to get every IP address from login_log which doesn't have a row in ip_location.
I tried this query but it throws a syntax error.

SELECT login_log.ip 
FROM login_log 
WHERE NOT EXIST (SELECT ip_location.ip
                 FROM ip_location
                 WHERE login_log.ip = ip_location.ip)

ERROR: syntax error at or near "SELECT"
LINE 3: WHERE NOT EXIST (SELECT ip_location.ip`

I'm also wondering if this query (with adjustments to make it work) is the best performing query for this purpose.

Answer 1

There are basically 4 techniques for this task, all of them standard SQL.

NOT EXISTS

Often fastest in Postgres.

SELECT ip 
FROM   login_log l 
WHERE  NOT EXISTS (
   SELECT  -- SELECT list mostly irrelevant; can just be empty in Postgres
   FROM   ip_location
   WHERE  ip = l.ip
   );

Also consider:

• What is easier to read in EXISTS subqueries?

LEFT JOIN / IS NULL

Sometimes this is fastest. Often shortest. Often results in the same query plan as NOT EXISTS.

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;

EXCEPT

Short. Not as easily integrated in more complex queries.

SELECT ip 
FROM   login_log

EXCEPT ALL  -- "ALL" keeps duplicates and makes it faster
SELECT ip
FROM   ip_location;

Note that (per documentation):

duplicates are eliminated unless EXCEPT ALL is used.

Typically, you'll want the ALL keyword. If you don't care, still use it because it makes the query faster.

NOT IN

Only good without null values or if you know to handle null properly. I would not use it for this purpose. Also, performance can deteriorate 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:

• Find records where join doesn't exist

Similar question on dba.SE targeted at MySQL:

• Select rows where value of second column is not present in first column

Answer 2

A.) The command is NOT EXISTS, you're missing the 'S'.

B.) Use NOT IN instead

SELECT ip 
  FROM login_log 
  WHERE ip NOT IN (
    SELECT ip
    FROM ip_location
  )
;

Answer 3

SELECT * FROM testcases1 t WHERE NOT EXISTS ( SELECT 1
FROM executions1 i WHERE t.tc_id = i.tc_id and t.pro_id=i.pro_id and pro_id=7 and version_id=5 ) and pro_id=7 ;

Here testcases1 table contains all datas and executions1 table contains some data among testcases1 table. I am retrieving only the datas which are not present in exections1 table. ( and even I am giving some conditions inside that you can also give.) specify condition which should not be there in retrieving data should be inside brackets.

Answer 4

this can also be tried...

SELECT l.ip, tbl2.ip as ip2, tbl2.hostname
FROM   login_log l 
LEFT   JOIN (SELECT ip_location.ip, ip_location.hostname
             FROM ip_location
             WHERE ip_location.ip is null)tbl2