Are these SQL queries identical - if not whats the difference

Question

Assuming the proper tables are in place - what is the difference between the two queries - when would they return different results

select *
from quiz_user qu
join quiz_topic qt on qu.quiz_id = qt.quiz_id and qt.topic_id=44
where qu.user_id=9

select *
from quiz_user qu
join quiz_topic qt on qu.quiz_id = qt.quiz_id 
where qu.user_id=9
and qt.topic_id=44

Thanks

please see the link.. cheers.. http://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause — RoMEoMusTDiE, May 12 '12 at 12:00

score 3 · Accepted Answer · answered May 12 '12 at 11:30

3

I don't see any difference in queries as they are. However, if you used LEFT JOIN instead of INNER (default), results would be different.

answered May 12 '12 at 11:30

a1ex07

36,826
12
90
103

Thanks - so if the query was a left join then how would the results differ – Gublooo May 12 '12 at 14:56
@Gublooo: In case of `LEFT JOIN` you will very likely get more rows (all rows from `quiz_user` will be included in resultset regardless of whether corresponding rows in `quiz_topic` exist or not) – a1ex07 May 13 '12 at 11:20

score 2 · Answer 2 · answered May 12 '12 at 11:36

2

None, but the second code's intent is clearer, JOIN is merely used to reflect the data relationship, not for filtering.

On first code's JOIN, it would seem the number 44 has a bearing on quiz_topic's relationship to quiz_user

answered May 12 '12 at 11:36

Michael Buen

38,643
9
94
118

Thanks - yep agree with u - not a good idea to use join to specify filter conditions – Gublooo May 12 '12 at 15:22

Are these SQL queries identical - if not whats the difference

2 Answers2