When Inner Join is useful?

[Crazyhorse]

What's the benefit of this INNER JOIN statement:

SELECT ContactsList.Name
FROM ContactsList
INNER JOIN TaskContacts
ON ContactsList.ContactID=TaskContacts.ContactID
WHERE TaskID=1

over this conceptually simpler, shorter SELECT WHERE statement:

SELECT ContactsList.Name
FROM ContactsList, TaskContacts
WHERE ContactsList.ContactID=TaskContacts.ContactID
AND TaskID=1

When is INNER JOIN going to provide any benefit at all?

 

[Michel Walsh]

For a good optimizer, there should be no difference. You can get a
difference if you have to switch to outer join, or mixing inner/outer join
since, in theory, the join should be logically performed BEFORE the where
clause get something to work onto. In fact, you may find the following even
MORE PERFORMANT than the two statements you presented (mainly if there is no
index on ContactID):


SELECT ContactsList.Name
FROM ContactsList
INNER JOIN (SELECT * FROM TaskContacts WHERE TaskID=1) AS x
ON ContactsList.ContactID=x.ContactID


But again, which statement is really the faster, if any, depends on the
optimizer, tables size, the presence or not of indexes, etc.


Also, it is not merely a problem of performance, but a matter of similarity
between INNER and OUTER join syntax: if you have a condition between two
tables, that goes in the JOIN, rather than in the WHERE clause.



Vanderghast, Access MVP

 

[John W. Vinson]

What's the benefit of this INNER JOIN statement:

SELECT ContactsList.Name
FROM ContactsList
INNER JOIN TaskContacts
ON ContactsList.ContactID=TaskContacts.ContactID
WHERE TaskID=1

over this conceptually simpler, shorter SELECT WHERE statement:

SELECT ContactsList.Name
FROM ContactsList, TaskContacts
WHERE ContactsList.ContactID=TaskContacts.ContactID
AND TaskID=1

When is INNER JOIN going to provide any benefit at all?

With JET, the INNER JOIN query will be updateable and the Cartesian join query
will not.