0 votes
1 view
in SQL by (20.3k points)

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;

1 Answer

0 votes
by (40.3k points)

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;

Related questions

0 votes
1 answer
asked Oct 29, 2019 in Java by Anvi (10.2k points)
+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)
+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...