Nested SELECT with IN

Question

I am using Mysql and trying to migrate data from one DB to other. I have not worked on databases earlier.

This query gives me close to 300 results

select distinct internal_id from ratingsapp.hotel03

But this one returns no results and has no errors either:

select restname from City.resturant where restid not in 
    (select distinct internal_id from ratingsapp.hotel03)

Now if I manually pick few internal_ids from the hotel03 table, and put this in place of the nested query, the query returns proper results. I am not sure what exactly am I doing wrong.

score 1 · Accepted Answer · answered Jun 15 '14 at 14:06

This usually happens when one of the values is NULL. So this might work:

select restname
from City.resturant
where restid not in (select distinct internal_id from ratingsapp.hotel03 where internal_id is not null);

Another way to write this query is with not exists:

select restname
from City.resturant r
where not exists (select 1
                  from ratingsapp.hotel03 h
                  where h.internal_id = r.restid
                 );

The way this works, NULL is handled correctly without directly checking for it. That is one reason why NOT EXISTS is preferable to NOT IN.

score 0 · Answer 2 · answered Jun 15 '14 at 14:11

Are you sure it is not because all the restid from City.restaurant are in internal_id of ratingsapp.hotel03 ? You say that you manually pick a few of these ids and there was a result, but check this:

distinct City.restaurant.restid: 1, 2, 3, 4, 5
distinct ratingsapp.hotel03.internal_id: 1, 2, 3, 4, 5

Then your query will return nothing, as all the restid are not not in the internal_id. But if you pick a few ids from ratingsapp.hotel03.internal_id, for instance:

select restname
from City.resturant
where restid not in (1, 2, 3)

Then you will have all the City.restaurant with a restid of 4 or 5!

Nested SELECT with IN

2 Answers2