locked
Understanding INNER JOIN RRS feed

  • Question

  • I have a Movie table and a director table.

    MovieTable = MovieID, DirectorID, MovieName, ActorID
    DirectorTable = DirectorID, DirectorName

    If i do an inner join - which would be the correct way and why?

    select * from movieTable inner join DirectorTable on DirectorTable.DirectorID = MovieTable.DirectorID

    OR

    select * from movieTable inner join MovieTable on MovieTable.DirectorID = DirectorTable.DirectorID

    Are they the same or any rule that i should keep in mind when inner joins are created?

    Thanks

    Monday, September 17, 2012 8:10 PM

Answers

  • is there any foreign key relationship defined betwen 2 tables ? 

    if you have a FK relation between MovieTable.DirectorID to DirectorTable.DirectorID  

    assuming DirectorID is the primary key for DirectorTable  and MovieID is the primarykey for MovieTable .

    Select a.* --if if you need all data from movietable

    from dbo.MovieTable a 

    inner join dbo.DirectorTable b on a.DirectorID = b.DirectorID

    What output are you looking for ? your second query is wrong

    http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx can be a reference about joins

    IF it is a typo for 2nd query then output would be same ! 

    Select b.* --if if you need all data from movietable

    from dbo.DirectorTable a 

    inner join dbo.MovieTable b on a.DirectorID = b.DirectorID

    • Edited by JR1811 Monday, September 17, 2012 8:55 PM
    • Proposed as answer by DivasP Tuesday, September 18, 2012 4:58 AM
    • Marked as answer by Kalman Toth Friday, September 21, 2012 7:52 PM
    Monday, September 17, 2012 8:35 PM

All replies

  • is there any foreign key relationship defined betwen 2 tables ? 

    if you have a FK relation between MovieTable.DirectorID to DirectorTable.DirectorID  

    assuming DirectorID is the primary key for DirectorTable  and MovieID is the primarykey for MovieTable .

    Select a.* --if if you need all data from movietable

    from dbo.MovieTable a 

    inner join dbo.DirectorTable b on a.DirectorID = b.DirectorID

    What output are you looking for ? your second query is wrong

    http://msdn.microsoft.com/en-us/library/ms191517(v=sql.105).aspx can be a reference about joins

    IF it is a typo for 2nd query then output would be same ! 

    Select b.* --if if you need all data from movietable

    from dbo.DirectorTable a 

    inner join dbo.MovieTable b on a.DirectorID = b.DirectorID

    • Edited by JR1811 Monday, September 17, 2012 8:55 PM
    • Proposed as answer by DivasP Tuesday, September 18, 2012 4:58 AM
    • Marked as answer by Kalman Toth Friday, September 21, 2012 7:52 PM
    Monday, September 17, 2012 8:35 PM
  • In the second query, why there's a join on the same table (Movietable) but you are refererring in the ON clause on a different table (DirectorTable) ?

    Monday, September 17, 2012 8:43 PM
  • I do not think you will get benifit out of this.

    Check the execution plan of both query & you will get same out.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, September 17, 2012 8:44 PM
  • To answer your question (and assuming you have a typo in the 2nd query), they are the same.  Both ways are correct, and product the exact same output.  The way you write it comes down to personal preference.  I always write the left table on the left of the ON clause, but we have many procs/queries/views/etc where I work that put the left table on the right side.
    • Proposed as answer by Naomi N Thursday, September 20, 2012 4:19 AM
    Monday, September 17, 2012 8:53 PM
  • To answer your question (and assuming you have a typo in the 2nd query), they are the same.  Both ways are correct, and product the exact same output.  The way you write it comes down to personal preference.  I always write the left table on the left of the ON clause, but we have many procs/queries/views/etc where I work that put the left table on the right side.

    I agree - either way works and which table you put first is really personal preference unless your organization has standards defined for how that is done.

    I put the table being joined first - then the table it is joining to...

    SELECT ...
    FROM tablea a
    INNER JOIN tableb b ON b.ID = a.ID
    When you get a lot of tables involved in your query, it is easier for me to see which tables are related this way.  But again, that is just my personal preference.

    Jeff Williams

    Monday, September 17, 2012 11:21 PM