3

I recently came across this piece of SQL:

SELECT members.id
FROM members, members_to_groups
WHERE members.id = members_to_groups.memberId
    AND members_to_groups.groupId = 1

Now I just never would have thought of doing the join this way, instead using an inner join:

SELECT members.id
FROM members
INNER JOIN members_to_groups ON members.id = members_to_groups.memberId
WHERE members_to_groups.groupId = 1

Obviously the second way of doing it requries more code, but is there an objective preference to either method, and what one would execute the fastest?

Kai
  • 38,985
  • 14
  • 88
  • 103
mattdwen
  • 5,288
  • 10
  • 47
  • 61

3 Answers3

4

They will both execute at the same rate, but every time you use the first method the terrorists win.

The first method is a non-portable "old" version of sql, for bigger queries it is much more difficult to distinguish what in you WHERE clause is for joining tables, and what is for filtering.

Parris Varney
  • 11,320
  • 12
  • 47
  • 76
3

The only way you can know is by examining the execution plan, but implicit and explicit inner join lead to exactly the same execution plan, so there is no difference in performance. The explicit join is just much much more readable.

Also, @gbn says that implicit joins are not valid semantically. :)

Community
  • 1
  • 1
Jacob
  • 41,721
  • 6
  • 79
  • 81
0
WHERE members.id = members_to_groups.memberId => occurs at row level

whereas

INNER JOIN members_to_groups ON members.id = members_to_groups.memberId => occurs at table level

Since, joins occur at table level rather than on row level they are faster

Angelin Nadar
  • 8,944
  • 10
  • 43
  • 53