By dmanxiii


2008-09-04 22:49:58 8 Comments

Is there any efficiency difference in an explicit vs implicit inner join? For example:

SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;

vs.

SELECT a.*, b.*
FROM table a, table b
WHERE a.id = b.id;

11 comments

@lomaxx 2008-09-04 22:56:56

Performance wise, they are exactly the same (at least in SQL Server).

PS: Be aware that the IMPLICIT OUTER JOIN syntax is deprecated since SQL Server 2005. (The IMPLICIT INNER JOIN syntax as used in the question is still supported)

Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing

@J Wynia 2008-09-05 00:01:45

@lomaxx, just for clarity's sake, could you specify which syntax of the 2 in the question is deprecated?

@Cade Roux 2008-10-21 20:50:08

implicit join syntax is supported by SQL Server 2005 out of the box, but yes, it's a bad idea.

@BlackWasp 2008-12-28 14:44:39

Although I prefer the explicit syntax, can you explain how can they be deprecating implicit joins? The idea that it could be deprecated seems odd and the suggestion that they aren't supported by SQL 2K5 is not corrrect.

@NotMe 2009-05-20 14:28:54

Can you provide supporting documentation? This sounds wrong on multiple levels.

@David Crawshaw 2009-09-30 09:10:29

How do you deprecate the SQL standard?

@Lasse Vågsæther Karlsen 2009-09-30 09:10:31

They are deprecating outer joins with the old syntax, not inner joins. The reason is that they are problematic to get correct, and are in some cases possible to satisfy with different execution plans that produce different results.

@HLGEM 2010-06-21 20:00:38

@david Crenshaw, the implicit join is no longer in the standard and hasn't been for 18 years.

@Marcus Adams 2010-06-24 16:25:25

In standard SQL, an implicit join isn't an outer join. It's either an inner join or a cross join.

@Alexander Bird 2011-02-08 04:30:21

Please provide supporting evidence and let me know somehow and I'll remove the downvote. Or at least mention the test and measurement that you used.

@onedaywhen 2011-09-28 17:08:16

So-called "implicit joins" of the 'inner' or 'cross' variety remain in the Standard. SQL Server is deprecating the "old-style" outer join syntax (i.e. *= and =*) which has never been Standard.

@Jeyanth Kumar 2012-03-16 08:36:39

this is wrong.. if JOIN is not used there is problem in creating more number of rows by cross product. use EXPLAIN keyword to know the difference about the queries.

@Jens Mühlenhoff 2013-10-13 13:48:26

I've improved the answer with an external link and a clear distinction between the deprecated implict outer join syntax and the implicit inner join syntax that is used in the question.

@ypercubeᵀᴹ 2015-06-18 09:06:55

@HLGEM this is incorrect. Implicit join syntax (FROM a, b, c) is not being deprecated. It's still in 2003+ SQL standards.

@philipxy 2017-08-22 21:53:08

Old (+) and *= outer join syntax was never part of standard SQL. And it has nothing to do with this question.

@Sean 2015-08-13 18:09:17

In my experience, using the cross-join-with-a-where-clause syntax often produces a brain damaged execution plan, especially if you are using a Microsoft SQL product. The way that SQL Server attempts to estimate table row counts, for instance, is savagely horrible. Using the inner join syntax gives you some control over how the query is executed. So from a practical point of view, given the atavistic nature of current database technology, you have to go with the inner join.

@cimmanon 2015-08-13 18:17:35

Do you have any proof of this? Because the accepted answer says otherwise.

@Matt Fenwick 2012-04-25 01:43:26

On MySQL 5.1.51, both queries have identical execution plans:

mysql> explain select * from table1 a inner join table2 b on a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.02 sec)

mysql> explain select * from table1 a, table2 b where a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref          | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL | NULL    | NULL         |  986 |       |
|  1 | SIMPLE      | a     | ref  | pid           | pid  | 4       | schema.b.pid |   70 |       |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.00 sec)

table1 has 166208 rows; table2 has about 1000 rows.

This is a very simple case; it doesn't by any means prove that the query optimizer wouldn't get confused and generate different plans in a more complicated case.

@David 2011-11-30 18:30:32

Performance wise, it should not make any difference. The explicit join syntax seems cleaner to me as it clearly defines relationships between tables in the from clause and does not clutter up the where clause.

@deadbug 2008-09-04 23:39:08

@lomaxx: Just to clarify, I'm pretty certain that both above syntax are supported by SQL Serv 2005. The syntax below is NOT supported however

select a.*, b.*  
from table a, table b  
where a.id *= b.id;

Specifically, the outer join (*=) is not supported.

@HLGEM 2009-03-18 17:21:32

Frankly I wouldn't use it even in SQL Server 2000, the *= syntax often gives wrong answers. Sometimes it interprets these as cross joins.

@edosoft 2008-11-25 14:13:22

The second syntax has the unwanted possibility of a cross join: you can add tables to the FROM part without corresponding WHERE clause. This is considered harmful.

@Jus12 2015-09-02 16:59:19

What if the table names in the from clause are generated from the tables used in the where clause?

@Mike McAllister 2008-09-17 17:44:01

As Leigh Caldwell has stated, the query optimizer can produce different query plans based on what functionally looks like the same SQL statement. For further reading on this, have a look at the following two blog postings:-

One posting from the Oracle Optimizer Team

Another posting from the "Structured Data" blog

I hope you find this interesting.

@SquareCog 2008-10-30 01:34:03

Mike, the difference they are talking about is that you need to be sure that if you specify an explicit join, you specify the join condition to join on, not the filter. You will note that for semantically correct queries, the exec plan is the same.

@andy47 2008-09-07 09:55:30

The first answer you gave uses what is known as ANSI join syntax, the other is valid and will work in any relational database.

I agree with grom that you should use ANSI join syntax. As they said, the main reason is for clarity. Rather than having a where clause with lots of predicates, some of which join tables and others restricting the rows returned with the ANSI join syntax you are making it blindingly clear which conditions are being used to join your tables and which are being used to restrict the results.

@Leigh Caldwell 2008-09-04 23:38:08

On some databases (notably Oracle) the order of the joins can make a huge difference to query performance (if there are more than two tables). On one application, we had literally two orders of magnitude difference in some cases. Using the inner join syntax gives you control over this - if you use the right hints syntax.

You didn't specify which database you're using, but probability suggests SQL Server or MySQL where there it makes no real difference.

@SquareCog 2008-10-30 01:26:29

Leigh, you can use the hints in implicit joins too.

@Jon Heller 2013-06-24 22:49:28

In Oracle it is extremely rare for the join order to affect the execution plan in a meaningful way. See this article by Jonathan Lewis for an explanation.

@Joshdan 2008-09-04 23:33:25

Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating this join syntax and it's not supported by sql server2005 out of the box.

I think you are thinking of the deprecated *= and =* operators vs. "outer join".

I have just now tested the two formats given, and they work properly on a SQL Server 2008 database. In my case they yielded identical execution plans, but I couldn't confidently say that this would always be true.

@grom 2008-09-04 23:23:25

Personally I prefer the join syntax as its makes it clearer that the tables are joined and how they are joined. Try compare larger SQL queries where you selecting from 8 different tables and you have lots of filtering in the where. By using join syntax you separate out the parts where the tables are joined, to the part where you are filtering the rows.

@villasv 2017-11-29 13:01:50

I completely agree, but this is kind of off-topic. OP asked about efficiency.

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

16 Answered Questions

[SOLVED] SQL join: where clause vs. on clause

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

3 Answered Questions

13 Answered Questions

[SOLVED] When should I use cross apply over inner join?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

20 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

37 Answered Questions

Sponsored Content