Difference between JOIN and WHERE for two identical queries

Question

I am getting difficulty understanding the following two queries

select QuestionSetQuestionAnswer.*
from QuestionSetQuestionAnswer
inner join QuestionAnswer 
    on QuestionAnswer.ID = QuestionSetQuestionAnswer.SelectedAnswerID 
        and QuestionAnswer.IsCorrect = 'true' 
where QuestionSetQuestionAnswer.QuestionID = (
                select QuestionID 
                from QuestionSetQuestion 
                where SetID = '45e20157-0030-c58b-55c4-08d11c725bd7'
                                             )
select QuestionSetQuestionAnswer.* 
from QuestionSetQuestionAnswer
inner join QuestionSetQuestion 
    on QuestionSetQuestion.QuestionID = QuestionSetQuestionAnswer.QuestionID 
        and QuestionSetQuestion.SetID = '45e20157-0030-c58b-55c4-08d11c725bd7'
inner join QuestionAnswer 
    on QuestionAnswer.ID = QuestionSetQuestionAnswer.SelectedAnswerID 
        and QuestionAnswer.IsCorrect = 'true'

What is the difference between the two queries? Are they identical?

score 0 · Accepted Answer · answered Apr 02 '14 at 13:02

The first query looks for a set ID in QuestionSetQuestion and expects to find zero or one record matching. - If it finds one record, it shows all QuestionSetQuestionAnswer matching the found question ID, multiplied with the number of correct answers (which is assumed to be zero or one I guess). - If it finds no record, then no record is shown. - If it finds more than one records than a runtime error occurs. So the set ID should be unique for the table. (This looks a bit dubious.)

The second does the same, except for the case there are multiple matches on the set ID. - If it finds one or more records, it shows all QuestionSetQuestionAnswer matching the found question ID, multiplied with the number of correct answers (which I guessed above to be supposed to be zero or one) multiplied with the number of found QuestionSetQuestion (which was assumed to be zero or one in the first query). - If it finds no record, then no record is shown.

So, provided we always find one or zero records with the given set ID, both statements do the same. Neither is written well in my opinion, as only records of one table shall be shown, so why join the other tables at all? I guess this is what is meant:

select *
from QuestionSetQuestionAnswer
where QuestionID = -- or IN if multiple matches are possible
(
  select QuestionID 
  from QuestionSetQuestion 
  where SetID = '45e20157-0030-c58b-55c4-08d11c725bd7'
)
and exists 
(
  select *
  from QuestionAnswer 
  where ID = QuestionSetQuestionAnswer.SelectedAnswerID and IsCorrect = 'true' 
);

Difference between JOIN and WHERE for two identical queries

1 Answers1