MySQL Forums
Forum List  »  General

Convert WHERE IN to JOINS not same results
Posted by: Daniel van Seggelen
Date: February 14, 2018 12:32AM

I have a large tabele of 900 000 rows and now using this query.

SELECT * ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
WHERE `to` in (select profile_id from `user_profiles` where is_fake=1 and user_id=1)
and `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5


This takes about one min. I tried to convert this to a join here:

SELECT a.* ,
b.profile_id as from_id ,
b.profile_name as from_name ,
b.profile_image as from_image,
b.gender as from_gender ,
c.profile_id as to_id ,
c.profile_name as to_name ,
DATE_FORMAT(time_sending,'%b %d %Y %h:%i %p') as time_sending
FROM `messages` a
LEFT JOIN user_profiles b ON a.from = b.profile_id
LEFT JOIN user_profiles c ON a.`to` = c.profile_id
LEFT JOIN user_profiles fake ON fake.profile_id = a.to and fake.is_fake=1 and fake.user_id = 1
WHERE `_initiator` ='unanswered' and hide = 0
order by `time_sending` desc
LIMIT 5

But I get different result. I have no idea WHY this is like that.
What is wrong with this?

Same this a NOT IN.

How to deal with this?

select * from messages a LEFT JOIN user_profiles b ON a.`to` = b.profile_id WHERE `to` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 ) and `from` in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 ) and `hide` not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )


Converted to

select * from messages a LEFT JOIN user_profiles b ON a.to = b.profile_id WHERE b.is_fake=0 and a.from = b.profile_id and a.hide not in (SELECT profile_id FROM `user_profiles` WHERE `is_fake`=0 )

Also different result.

Why is left join not working here?

Options: ReplyQuote


Subject
Written By
Posted
Convert WHERE IN to JOINS not same results
February 14, 2018 12:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.