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.

Solution 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

Solution 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
  )
;

Solution 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.

Solution 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

View more solutions

Comments

[Teja]

Which SQL would run faster considering the data volumes are high in both tables. ( assuming in billions )

[Istiaque Ahmed]

WHERE ip_location.ip is null - how can the WHEREcondition be ever true ? Also, the sub-query is not a correlated one .

[Grzegorz Grabek]

NOT IN on large datasets is a terrible idea. Very, very slow. It is bad and should be avoided.

[Dan Parker]

EXCEPT ALL was fastest for me

[Matthias Fripp]

Be careful with LEFT JOIN — if there are multiple matching rows in the lookup table, this will create a duplicate entry in your main query for each matching row, which may not be wanted.

[Erwin Brandstetter]

@MatthiasFripp: Except that this can never occur with WHERE i.ip IS NULL, meaning no match at all.

[Matthias Fripp]

@erwin-brandstetter: Good point. I tripped myself up thinking about the possibility of multiple positive matches, but of course those would all be excluded.

[TheRealChx101]

@GrzegorzGrabek Try to offer alternatives instead of just dismissing other people's answers

[Grzegorz Grabek]

@TheRealChx101 comment was written according to the suggestion of Celeb to use NOT IN instead NOT EXISTS. It (comment) is perfectly clear for most ppl as you can see how many ppl vote this comment useful.