<p>When I'm joining three or more tables together by a common column, I'd write my query like this:</p>

<pre><code>SELECT *
FROM   a, b, c
WHERE  a.id = b.id
AND    b.id = c.id
</code></pre>

<p>a colleague recently asked my why I didn't do explicit <em>Join Transitive Closure</em> in my queries like this:</p>

<pre><code>SELECT *
FROM   a, b, c
WHERE  a.id = b.id
AND    b.id = c.id
AND    c.id = a.id
</code></pre>

<p>are the really any advantages to this? Surely the optimiser can imply this for itself?</p>

<p><em>edit: I know it's evil syntax, but it's a quick and dirty example of legitimate legacy code +1 @<a href="https://stackoverflow.com/questions/11028/what-are-the-advantages-of-explicit-join-transitive-closure-in-sql#11114">Stu</a> for cleaning it up</em></p>

[{'answer_id': 11031, 'author': 'Nick Berardi', 'author_id': 17, 'author_profile': 'https://Stackoverflow.com/users/17', 'pm_score': 2, 'selected': False, 'text': '<p>No this syntax stems from the days before joins were in the language.  Not sure of the problems associated with it, but there are definitely language constructs that are more supported for jointing tables.</p>\n'}, {'answer_id': 11035, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 1, 'selected': False, 'text': '<p>If you look at it from a mathematical point of view, your examples should yield the same results.</p>\n\n<p>a = b = c</p>\n\n<p>So your first example would yield the same results as the second, so no need to do the extra work.</p>\n'}, {'answer_id': 11038, 'author': 'Lars Mæhlum', 'author_id': 960, 'author_profile': 'https://Stackoverflow.com/users/960', 'pm_score': 1, 'selected': False, 'text': '<p>I just want to say that this kind of joining is the devils work.<br>\nJust think about it; the conditions for joining and filtering gets mixed together in the where statement.<br>\nWhat happens when you need to join across 20 tables and filter on 15 values?</p>\n\n<p>Again, just my $.02</p>\n'}, {'answer_id': 11041, 'author': 'Keith', 'author_id': 905, 'author_profile': 'https://Stackoverflow.com/users/905', 'pm_score': 1, 'selected': False, 'text': '<p>In Microsoft SQL the query plans for these two queries are identical - they are executed in the same way.</p>\n'}, {'answer_id': 11044, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 3, 'selected': True, 'text': "<p>You don't need to do this in todays database engines, but there was a time when things like that would give the query optimizer more hints as to possible index paths and thus to speedier results.</p>\n\n<p>These days that entire syntax is going out anyway.</p>\n"}, {'answer_id': 11114, 'author': 'Stu', 'author_id': 414, 'author_profile': 'https://Stackoverflow.com/users/414', 'pm_score': 2, 'selected': False, 'text': "<p>This is filthy, evil legacy syntax. You write this as</p>\n\n<blockquote>\n<pre><code>Select\n  *  -- Oh, and don't ever use *, either\nFrom\n  A \n  Inner Join B On A.ID = B.ID\n  Inner Join C On B.ID = C.ID\n</code></pre>\n</blockquote>\n"}, {'answer_id': 11410, 'author': 'Rad', 'author_id': 1349, 'author_profile': 'https://Stackoverflow.com/users/1349', 'pm_score': -1, 'selected': False, 'text': '<p>That syntax has its uses though ... there are times when you find you need to join two tables on more than one field</p>\n'}, {'answer_id': 519910, 'author': 'sdfx', 'author_id': 3445, 'author_profile': 'https://Stackoverflow.com/users/3445', 'pm_score': 0, 'selected': False, 'text': '<p>This question is similar to this one here with a very in-depth explanation:</p>\n\n<p><a href="https://stackoverflow.com/questions/397089/sql-question-from-joel-spolsky-article">SQL question from Joel Spolsky article</a></p>\n\n<p>The short answer is, that the explicit declaration of the transitiv property may speed the query up. This is because query optimization is not a trivial task and some SQL servers might have problems with it.</p>\n'}]
['https://Stackoverflow.com/questions/11028', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1030/']