CODEWITHSUNDEEP
mysqljoinwhere-clause
michael
michael

Reputation: 308

Join conditions ON vs WHERE

probably a dumb question, but why are these two mysql queries giving different results?

select * from table1 p join table2 m on p.mid=m.mid and m.onw=1

select * from table1 p join table2 m on p.mid=m.mid where m.onw=1

the former gives about 5 times more rows. doesn't the "and m.onw=1" limit the same way as the "where" clause in latter query?

the actual live query executed from phpmyadmin is:

select * from xcart_products p join xcart_manufacturers m on p.manufacturerid=m.manufacturerid and m.onwalmart=1 
 (197023 total)

versus

select * from xcart_products p join xcart_manufacturers m on p.manufacturerid=m.manufacturerid where m.onwalmart=1 
(38996 total)

UPDATE. seems to be a bug in phpmyadmin. if i use count(*) instead of * then results are same. perhaps count(*) causes less rows to be returned?

Upvotes: 1

Views: 291

Answers (2)

Maverick
Maverick

Reputation: 1185

Yes, it should give the same number of rows regardless where you put your filter.

First Query: It will join 2 tables and filter m.onw=1

Second Query: It will join 2 tables and later filter on m.onw=1

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

Its better to add it into join statement rather than adding it as where clause from the performance point it will be better

Upvotes: 0

Related Questions