SQL Select and find rows that not exists in other table

Question

I have two tables with phone numbers.

I want check if there is new rows in addresbook table that not exist in users table.

As I am newbie with SQL, is this query correct?

SELECT * 
FROM addressbook
WHERE NOT EXISTS (
  SELECT Phone1
  FROM users 
  WHERE addressbook.phone = users.phone1
)

EDIT: I user MySQL with PHPMyAdmin Interface, sorry for not to specify before

Take a look at this: http://stackoverflow.com/questions/173041/not-in-vs-not-exists — user1141796, Dec 20 '13 at 10:55

peter.petrov · Accepted Answer · 2013-12-20T11:01:35.260

5

Seems OK but I would do it like this.

select a.* from addressbook a
left outer join users u on a.phone = u.phone1
where
(u.phone1 is null)

This is simpler and probably faster.

edited Dec 20 '13 at 11:01

answered Dec 20 '13 at 10:55

peter.petrov

In question there is clearly define, that he/she want to found those addresses which are not exist (by Phone) in User Table. – Vishal Patel Dec 20 '13 at 11:05
@VishalPatel Yes, and this is what my query does. – peter.petrov Dec 20 '13 at 11:12
my query also dose same. You have used left outer join and I have used `Not In` – Vishal Patel Dec 20 '13 at 11:18
@VishalPatel Yes, that's obvious from the answers we gave. – peter.petrov Dec 20 '13 at 11:23

score 1 · Answer 2 · answered Dec 20 '13 at 10:56

1

In SQL Server your query is

SELECT * from addressbook WHERE Phone NOT in (SELECT Phone1 from users)

answered Dec 20 '13 at 10:56

Vishal Patel

OP does not want "inner join" but the opposite. – peter.petrov Dec 20 '13 at 10:57
@peter.petrov what you say? – Vishal Patel Dec 20 '13 at 10:58
"I want check if there is new rows in addresbook table that not exist in users table." He needs left outer join from AddressBook to Users. And your two queries are not logically identical. This is what I say. – peter.petrov Dec 20 '13 at 10:59

2 Answers2