INNER JOIN vs multiple table names in "FROM"

Question

Possible Duplicate:
INNER JOIN versus WHERE clause — any difference?

What is the difference between an INNER JOIN query and an implicit join query (i.e. listing multiple tables after the FROM keyword)?

For example, given the following two tables:

CREATE TABLE Statuses(
  id INT PRIMARY KEY,
  description VARCHAR(50)
);
INSERT INTO Statuses VALUES (1, 'status');

CREATE TABLE Documents(
  id INT PRIMARY KEY,
  statusId INT REFERENCES Statuses(id)
);
INSERT INTO Documents VALUES (9, 1);

What is the difference between the below two SQL queries?

From the testing I've done, they return the same result. Do they do the same thing? Are there situations where they will return different result sets?

-- Using implicit join (listing multiple tables)
SELECT s.description
FROM Documents d, Statuses s
WHERE d.statusId = s.id
      AND d.id = 9;

-- Using INNER JOIN
SELECT s.description
FROM Documents d
INNER JOIN Statuses s ON d.statusId = s.id
WHERE d.id = 9;

No difference. Second is just nicer syntax that makes it clearer what the join condition is without having to look elsewhere and can help avoid inadvertent cross joins. — Martin Smith, Feb 25 '11 at 14:49

score 16 · Accepted Answer · edited Mar 09 '11 at 09:16

16

There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it doesn't work correctly right now anyway) differs from vendor to vendor. Since you shouldn't mix implicit and explict joins in the same query (you can get wrong results), needing to change something to a left join means rewriting the entire query.

edited Mar 09 '11 at 09:16

onedaywhen

55,269
12
100
138

answered Feb 25 '11 at 15:01

HLGEM

94,695
15
113
186

1

"is even deprecated in SQL Server" -- citation, please. – onedaywhen Feb 25 '11 at 15:44
look in books online search for deprecated features [SQL Server] – HLGEM Feb 25 '11 at 15:53
I see `Use of *= and =*` is deprecated (http://msdn.microsoft.com/en-us/library/ms143729.aspx) but that's analogous to `OUTER JOIN`. Can you tell me what wording I'm looking for that is analogous to `INNER JOIN`? Thanks again. – onedaywhen Feb 25 '11 at 16:10
...I must say I'm finding it hard to believe that the SQL Server team would deprecate a core SQL-92 feature. – onedaywhen Feb 25 '11 at 16:12
1

I didn't say the inner join was deprecated only the outer join. – HLGEM Feb 25 '11 at 16:14
Your sentence was ambiguous: with two and's with no commas between, it could by read (indeed was by me) as "the implicit join syntax is deprecated in SQL Server". I've attempted a rewrite. – onedaywhen Mar 09 '11 at 09:18
Why is it always never or always ever. There's always possibilities. For instance, an indexed view doesn't allow outer joins so I need to rethink my queries that use outer join to use inner joins. After researching inner joining on nulls, I found that using an implicit join offers the support i need. So, there's never no reason to use this or anything really. Sheesh.. – Levitikon Apr 06 '12 at 12:42

score 14 · Answer 2 · answered Feb 25 '11 at 14:49

14

If you do it the first way, people under the age of 30 will probably chuckle at you, but as long as you're doing an inner join, they produce the same result and the optimizer will generate the same execution plan (at least as far as I've ever been able to tell).

This does of course presume that the where clause in the first query is how you would be joining in the second query.

This will probably get closed as a duplicate, btw.

answered Feb 25 '11 at 14:49

Jordan

31,971
6
56
67

4

I am under 30, so I guess that is why I've always used INNER JOINs :). I think it looks more esthetically pleasing. – Michael Feb 25 '11 at 14:53

score 9 · Answer 3 · answered Feb 25 '11 at 15:00

The nice part of the second method is that it helps separates the join condition (on ...) from the filter condition (where ...). This can help make the intent of the query more readable.

The join condition will typically be more descriptive of the structure of the database and the relation between the tables. e.g., the salary table is related to the employee table by the EmployeeID column, and queries involving those two tables will probably always join on that column.

The filter condition is more descriptive of the specific task being performed by the query. If the query is FindRichPeople, the where clause might be "where salaries.Salary > 1000000"... thats describing the task at hand, not the database structure.

Note that the SQL compiler doesn't see it that way... if it decides that it will be faster to cross join and then filter the results, it will cross join and filter the results. It doesn't care what is in the ON clause and whats in the WHERE clause. But, that typically wont happen if the on clause matches a foreign key or joins to a primary key or indexed column. As far as operating correctly, they are identical; as far as writing readable, maintainable code, the second way is probably a little better.

score 3 · Answer 4 · answered Feb 25 '11 at 14:48

3

there is no difference as far as I know is the second one with the inner join the new way to write such statements and the first one the old method.

answered Feb 25 '11 at 14:48

MUG4N

19,377
11
56
83

1

It's 19 years old, it hardly the "New" way. – HLGEM Feb 25 '11 at 14:57
ok lets call it the updated way ;) – MUG4N Feb 25 '11 at 14:58

Matthew Cox · Answer 5 · 2011-02-25T16:17:45.780

3

The first one does a Cartesian product on all record within those two tables then filters by the where clause.

The second only joins on records that meet the requirements of your ON clause.

EDIT: As others have indicated, the optimization engine will take care of an attempt on a Cartesian product and will result in the same query more or less.

edited Feb 25 '11 at 16:17

answered Feb 25 '11 at 14:49

Matthew Cox

13,566
9
54
72

1

That's what the SQL says in English, but RDBMS engines are not that simplistic. The optimisation engine won't generate a cartesian product at all. – MatBailie Feb 25 '11 at 14:54
@Dems The answer is valid. That is what the SQL syntax indicates. What the optimization engine for a RDBMS does with the SQL syntax is another topic all together. Nevertheless, your statement about the engine optimizing it is valid. I'll add the annotation. – Matthew Cox Feb 25 '11 at 16:16
Agreed, I just meant to say that the way a problem is expressed does not always directly correlate to the actual actions taken by the RDBMS. It only enforces logic constraints on the result, not how the result is arrived at. – MatBailie Feb 25 '11 at 16:26

score 0 · Answer 6 · edited May 23 '17 at 12:02

0

A bit same. Can help you out.

edited May 23 '17 at 12:02

Community

1
1

answered Feb 25 '11 at 14:50

Saurabh Gokhale

53,625
36
139
164

score 0 · Answer 7 · answered Feb 25 '11 at 14:50

0

In the example you've given, the queries are equivalent; if you're using SQL Server, run the query and display the actual exection plan to see what the server's doing internally.

answered Feb 25 '11 at 14:50

Dave

3,581
1
22
25

INNER JOIN vs multiple table names in "FROM"

7 Answers7

Linked

Related