MySQL: finding a record not present in another table

Question

I have 2 MySQL tables:

id customer ipAddress -------------------------- 1 acme 10.10.10.10 2 target 33.33.33.33 number ip -------------------- 54321 10.10.10.10 41247 33.33.33.33 62068 77.77.77.77

In this case, 77.77.77.77 has no entries in table 1.

How can I get all the numbers in table 2 that do not have an ipAddress in table 1?

I have tried:

select ip from table1,table2 where ip not in(select ipAddress from table1);

but I get an empty set.

Change this select ip from table1,table2 to this select ip from table2. You don't need the extra join to table1 in the FROM clause. — Taryn
– Taryn, Commented Feb 13, 2015 at 21:22

Pierre-Olivier Benoit · Accepted Answer · 2015-02-13 21:27:41Z

I got a correct answer (77.77.77.77) with a left join and a where is null:

select ip from table2 left join table1 on (ip = ipAddress) where ipAddress is null;

PM 77-1 · Accepted Answer · 2015-02-13 21:34:22Z

"Records without match" problems very often can be solved with a simple OUTER JOIN and then WHERE ... IS NULL.

In you case:

SELECT t2.ip FROM table1 t1 RIGHT JOIN table2 t2 ON t1.ipAddress = t2.ip WHERE t1.ipAddress IS NULL ;

SQL Fiddle DEMO

Collectives™ on Stack Overflow

MySQL: finding a record not present in another table

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related