Which one is faster?
SELECT FROM A INNER JOIN B ON A.ID = B.ID
...or:
SELECT FROM A , B WHERE A.ID = B.ID
views:
196answers:
7
+4
A:
Measure the time needed for the execution. It depends a lot from too many parameters to be answered without any doubt.
Boris Pavlović
2009-10-08 08:11:04
A:
Aren't they both equally fast? Because they basically translate to the same thing....
If you want to know for sure you could probably set up a simple test case and execute it.
Quagmire
2009-10-08 08:11:52
+19
A:
I don't think one is faster than the other, but one is BETTER to use than the other:
SELECT (fields)
FROM A
INNER JOIN B ON A.ID = B.ID
is definitely the preferred way of expressing this (and conforms to the ANSI SQL standard for join syntax). It's clearer, it's more obvious to the observer what exactly is happening here.
Always use this syntax over the other - it's just easier and clearer!
PS: SQL Guru Aaron Bertrand seems to agree :-) Bad habits to kick: using old-style JOINs
marc_s
2009-10-08 08:12:02
always use this syntax and never answer the question asked ;-) Takes some effort not to downvote ;-)
Michael Krelin - hacker
2009-10-08 08:15:38
I said that I don't think there's any difference in performance - first sentence!
marc_s
2009-10-08 08:16:32
+1 because this is much clearer and also makes sure that join conditions and where clauses do not get confused. Also, the join order can be controlled in a more explicit way.
Lucero
2009-10-08 08:19:12
Mark, wish I could downvote my own comment for inability to read ;-)
Michael Krelin - hacker
2009-10-08 08:24:26
@hacker: no worries! We all tend to miss details at times :-)
marc_s
2009-10-08 08:27:20
As the far (perhaps very far) outlier here, I find the other syntax easier to read, every time.
Dean J
2009-10-08 14:03:38
Dean J: I find them about equal for two tables with one or two normal where clauses or less, but the moment a third table is added, I find the INNER JOIN syntax much easier to follow.
R. Bemrose
2009-10-08 14:07:52
Dean J also the old style syntax is much more prone to errors in complex queries where you can accidentally get a cross join. Further, the right and left join old style syntax (*= or =*) does not work correctly in SQl server and should never be used.
HLGEM
2009-10-08 14:51:27
@Yatendra: I'm not a java developer, I can't help you there, sorry.
marc_s
2010-03-07 16:11:37
+5
A:
They are equivalent, neither should be faster than the other. The best way to know is to use EXPLAIN.
Lukáš Lalinský
2009-10-08 08:12:12
I'd not call them "equivalent", because a where-clause join is not exactly the same as the join condition, especially for outer joins.
Lucero
2009-10-08 08:16:26
Yes, I know. Still, people tend to use the syntax they're used to for both inner and outer joins, so I thought that I'd mention it. Also, depending on the RDBMS used and how smart the optimizer is, the results (performance-wise) may differ if you have additional restrictions on the data to be joints (like, B.X=1). If you do this in the ON of the JOIN, it will clearly state that only those rows are to be used for joining, while the other way around it may also join first and then remove the non-matching rows.
Lucero
2009-10-08 08:24:04
I completely agree with that, but the two queries as they are written in the question are equivalent (both inner joins without any additional conditions). I wouldn't suggest to use the table1, table2 syntax either.
Lukáš Lalinský
2009-10-08 08:26:15
I concur with Lukáš, they are just the same. Use JOIN, it prevents accidental cartesian product on tables you are querying. And if you really want cartesian product on your table use tableA CROSS JOIN tableB, it makes the intent more obvious. Use JOIN to reflect the fact that you are relating tables to each other, don't use comma+WHERE combo for that. Use WHERE exclusively for filtering
Michael Buen
2009-10-08 08:43:13
Yes, they're equivalent. I'd be curious about any DBMS that produced DIFFERENT plans for those two statements.
Larry Lustig
2009-10-08 14:05:43
+2
A:
Check the execution plan for both queries, draw your conclusions from that.
thijs
2009-10-08 08:14:38
+6
A:
Measure, don't guess.
Some DBMS' will run explicit joins faster than implicit ones but it depends entirely upon the DBMS itself (the one I use is smart enough to do both at full speed).
There's a reason why we have DBAs. They're meant to monitor and tune the performance of the database based on reality, not some (mis-)conception as to how things might perform.
That's because the performance changes based on the data in the tables.
So, you should not be worrying about how fast those two queries perform, until there's a performance problem. Use your best guess (with indexes and such) but keep an eye on what the actual performance is, in production, and adjust for that.
See also here.
paxdiablo
2009-10-08 08:15:14
+2
A:
actually to be specific, neither of them are faster. They will not run at all.
You need to at least specify a column or constant or even * in the SELECT clause.
Squirrel
2009-10-08 14:01:55