Difference Between Explicit Join And Implicit Join?

Difference Between Explicit Join And Implicit Join? In theory， yes； in practice， no. The SQL-92 Standard says that the infixed join notation is executed in left-to-right order in the FROM clause. But the optimizer is free to re-arrange the query as long as the results are the same.

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates:

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

This is called ANSI SQL-89 join synax while

SELECT *
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID

is called SQL-92 Join.

Both syntax are still part of the latest ANSI SQL Standard and in terms of performance or resource there is no difference. Respecting that standard Microsoft has no plan to remove support for SQL 89 syntax in near future..

However, many experts favours ANSI SQL 92 syntax writing SQL Joins in FROM Clause as it is less confusing because
if you omit or forget join condition in the where clause then

SELECT *
FROM employee, department
--WHERE employee.DepartmentID = department.DepartmentID;

basically you will be doing a cross join however if you forget to write ON clause in FROM your query will throw an error.

Do not take my words for granted.... Read T-SQL fundaments By Itzik Ben-Gan Chapter 3: There is a whole paragraph on this topic....

Oh ya... if you think that WHERE clause is evalueated after FROM caluse so SQL 89 synax can be slower... no

There is something in SQL Server called query optimizer and it is very smart and it has ability to transform join condition in the where clause....before evaluating the query

This does not make a difference with INNER JOINs， but it is vital with OUTER JOINs.

There is a good story about this proposal. We needed the OUTER JOIN syntax； the proprietary methods in Oracle， SQL Server， Sybase，Informix， etc. were all screwed up. But after we had defined the OUTER JOIN， it was easy to extend the paper to INNER JOIN， UNION JOIN，NATURAL JOIN， etc. （most of which nobody implements）。

The rationale after the fact was that a product could have an option to force an order of execution with the infixed notation. Since SQL products now have some kind of optimizer， it is not really useful.

The real difference is in the mindset of programmers. Those that write with infixed notation thing in terms of a linear sequence of joins， as if they were limited to simple binary Theta operators. The programmers that use the older notation will use BETWEEN， IN （） and other predicates that work with multiple terms.