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:
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:
FROM student, department
WHERE student.DepartmentID = department.DepartmentID;