RSS Feed

Explicit vs. Implicit SQL Joins

Explicit vs. Implicit SQL Joins

The explicit join is easier to read and the implicit syntax is difficult to understand and more prone to errors. Moreover implicit syntax is now a day’s outdated.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation":

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

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

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 (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:

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

[Via]