Find records from one table which don t exist in another

Question

I ve got the following two tables  in MySQL   Phone book  ---- ------ --------------    id   name   phone number    ---- ------ --------------    1    John   111111111111    ---- ------ --------------    2    Jane   222222222222    ---- ------ --------------   Call  ---- ------ --------------    id   date   phone number    ---- ------ --------------    1    0945   111111111111    ---- ------ --------------    2    0950   222222222222    ---- ------ --------------    3    1045   333333333333    ---- ------ --------------   How do I find out which calls were made by people whose phone number is not in the Phone book  The desired output would be  Call  ---- ------ --------------    id   date   phone number    ---- ------ --------------    3    1045   333333333333    ---- ------ --------------

User · Answer

SELECT Call ID  Call date  Call phone number  FROM Call  LEFT OUTER JOIN Phone Book    ON  Call phone number Phone book phone number     WHERE Phone book phone number IS NULL   Should remove the subquery  allowing the query optimiser to work its magic   Also  avoid  SELECT    because it can break your code if someone alters the underlying tables or views  and it s inefficient

User · Answer

SELECT Call ID  Call date  Call phone number  FROM Call  LEFT OUTER JOIN Phone Book    ON  Call phone number Phone book phone number     WHERE Phone book phone number IS NULL   Should remove the subquery  allowing the query optimiser to work its magic   Also  avoid  SELECT    because it can break your code if someone alters the underlying tables or views  and it s inefficient

User · Answer

There s several different ways of doing this  with varying efficiency  depending on how good your query optimiser is  and the relative size of your two tables   This is the shortest statement  and may be quickest if your phone book is very short   SELECT    FROM    Call WHERE   phone number NOT IN  SELECT phone number FROM Phone book    alternatively  thanks to Alterlife   SELECT   FROM   Call WHERE  NOT EXISTS    SELECT      FROM   Phone book    WHERE  Phone book phone number   Call phone number    or  thanks to WOPR   SELECT    FROM   Call LEFT OUTER JOIN Phone Book   ON  Call phone number   Phone book phone number    WHERE Phone book phone number IS NULL    ignoring that  as others have said  it s normally best to select just the columns you want  not

User · Answer

The code below would be a bit more efficient than the answers presented above when dealing with larger datasets   SELECT   FROM Call WHERE  NOT EXISTS  SELECT  x  FROM Phone book where  Phone book phone number   Call phone number

User · Answer

SELECT Call ID  Call date  Call phone number  FROM Call  LEFT OUTER JOIN Phone Book    ON  Call phone number Phone book phone number     WHERE Phone book phone number IS NULL   Should remove the subquery  allowing the query optimiser to work its magic   Also  avoid  SELECT    because it can break your code if someone alters the underlying tables or views  and it s inefficient

User · Answer

The code below would be a bit more efficient than the answers presented above when dealing with larger datasets   SELECT   FROM Call WHERE  NOT EXISTS  SELECT  x  FROM Phone book where  Phone book phone number   Call phone number

User · Answer

There s several different ways of doing this  with varying efficiency  depending on how good your query optimiser is  and the relative size of your two tables   This is the shortest statement  and may be quickest if your phone book is very short   SELECT    FROM    Call WHERE   phone number NOT IN  SELECT phone number FROM Phone book    alternatively  thanks to Alterlife   SELECT   FROM   Call WHERE  NOT EXISTS    SELECT      FROM   Phone book    WHERE  Phone book phone number   Call phone number    or  thanks to WOPR   SELECT    FROM   Call LEFT OUTER JOIN Phone Book   ON  Call phone number   Phone book phone number    WHERE Phone book phone number IS NULL    ignoring that  as others have said  it s normally best to select just the columns you want  not

User · Answer

Alternatively   select id from call minus select id from phone number

User · Answer

I think   SELECT CALL   FROM CALL LEFT JOIN Phone book ON  CALL id   Phone book id WHERE Phone book name IS NULL

User · Answer

There s several different ways of doing this  with varying efficiency  depending on how good your query optimiser is  and the relative size of your two tables   This is the shortest statement  and may be quickest if your phone book is very short   SELECT    FROM    Call WHERE   phone number NOT IN  SELECT phone number FROM Phone book    alternatively  thanks to Alterlife   SELECT   FROM   Call WHERE  NOT EXISTS    SELECT      FROM   Phone book    WHERE  Phone book phone number   Call phone number    or  thanks to WOPR   SELECT    FROM   Call LEFT OUTER JOIN Phone Book   ON  Call phone number   Phone book phone number    WHERE Phone book phone number IS NULL    ignoring that  as others have said  it s normally best to select just the columns you want  not

User · Answer

The code below would be a bit more efficient than the answers presented above when dealing with larger datasets   SELECT   FROM Call WHERE  NOT EXISTS  SELECT  x  FROM Phone book where  Phone book phone number   Call phone number

User · Answer

SELECT name  phone number FROM Call a WHERE a phone number NOT IN  SELECT b phone number FROM Phone book b

User · Answer

SELECT DISTINCT Call id  FROM Call  LEFT OUTER JOIN Phone book USING  id   WHERE Phone book id IS NULL   This will return the extra id-s that are missing in your Phone book table

User · Answer

SELECT DISTINCT Call id  FROM Call  LEFT OUTER JOIN Phone book USING  id   WHERE Phone book id IS NULL   This will return the extra id-s that are missing in your Phone book table

User · Answer

SELECT Call ID  Call date  Call phone number  FROM Call  LEFT OUTER JOIN Phone Book    ON  Call phone number Phone book phone number     WHERE Phone book phone number IS NULL   Should remove the subquery  allowing the query optimiser to work its magic   Also  avoid  SELECT    because it can break your code if someone alters the underlying tables or views  and it s inefficient

User · Answer

The code below would be a bit more efficient than the answers presented above when dealing with larger datasets   SELECT   FROM Call WHERE  NOT EXISTS  SELECT  x  FROM Phone book where  Phone book phone number   Call phone number

User · Answer

There s several different ways of doing this  with varying efficiency  depending on how good your query optimiser is  and the relative size of your two tables   This is the shortest statement  and may be quickest if your phone book is very short   SELECT    FROM    Call WHERE   phone number NOT IN  SELECT phone number FROM Phone book    alternatively  thanks to Alterlife   SELECT   FROM   Call WHERE  NOT EXISTS    SELECT      FROM   Phone book    WHERE  Phone book phone number   Call phone number    or  thanks to WOPR   SELECT    FROM   Call LEFT OUTER JOIN Phone Book   ON  Call phone number   Phone book phone number    WHERE Phone book phone number IS NULL    ignoring that  as others have said  it s normally best to select just the columns you want  not

User · Answer

I think   SELECT CALL   FROM CALL LEFT JOIN Phone book ON  CALL id   Phone book id WHERE Phone book name IS NULL

User · Answer

SELECT name  phone number FROM Call a WHERE a phone number NOT IN  SELECT b phone number FROM Phone book b

User · Answer

SELECT t1 ColumnID  CASE      WHEN NOT EXISTS  SELECT t2 FieldText                        FROM Table t2                       WHERE t2 ColumnID   t1 ColumnID       THEN t1 FieldText     ELSE t2 FieldText END FieldText        FROM Table1 t1  Table2 t2

User · Answer

Alternatively   select id from call minus select id from phone number

User · Answer

SELECT t1 ColumnID  CASE      WHEN NOT EXISTS  SELECT t2 FieldText                        FROM Table t2                       WHERE t2 ColumnID   t1 ColumnID       THEN t1 FieldText     ELSE t2 FieldText END FieldText        FROM Table1 t1  Table2 t2

[sql] Find records from one table which don't exist in another

Examples related to sql

Examples related to mysql