Explicit vs implicit SQL joins

Question

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;

Answer 1

In SQL Server, explicit and implicit join’s performances are almost the same. 

Still, if you want to understand the differences between them, differences based on their notation are explained below. 

• 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.
• In ‘Explicit Join Notation’, ON keyword is used to specify the predicates for Join and JOIN keyword is used to specify the table to join. 

Example for Implicit:

SELECT *

FROM student INNER JOIN department 

ON student.DepartmentID = department.DepartmentID;

In ‘Implicit Join Notation’, tables that are supposed to be joined(SELECT statements FROM clause) are listed, commas are used to separate them. 

Therefore, this shows the cross-join, here WHERE clause applies additional filter predicates (i.e. in the explicit notation, functions that are comparable to Join- Predicates). 

Example for Explicit:

 SELECT *  

FROM   student, department 

WHERE  student.DepartmentID = department.DepartmentID;

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL course.