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

User · Answer

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

User · Answer

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

User · Answer

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  39 t exist   Similar question on dba SE targeted at MySQL    Select rows where value of second column is not present in first column

User · Answer

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

[sql] Select rows which are not present in other table

Examples related to sql

Examples related to postgresql

Examples related to null

Examples related to left-join

Examples related to exists