Menu
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; 
299
The implicit syntax is difficult to understand whereas, the explicit join is easier to read. To express joins such as 'Explicit Join Notation' and 'Implicit Join Notation' two different syntactical ways are defined in SQL.
SQL JOIN Implicit Join Joins can also be performed by having several tables in the from clause, separated with commas , and defining the relationship between them in the where clause. This technique is called an Implicit Join (since it doesn't actually contain a join clause).
Step 6: Explicit Join. This notation uses the ON keyword to specify the predicates for Join and the JOIN keyword to specify the tables to join. Join can be of any type i.e. INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN. It is easier to understand and less prone to errors.
Four types of joins: left, right, inner, and outer.
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
97
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.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
