SQL Joins


Example

Explicit joins should always be used; implicit joins have several problems:

  • The join condition is somewhere in the WHERE clause, mixed up with any other filter conditions. This makes it harder to see which tables are joined, and how.

  • Due to the above, there is a higher risk of mistakes, and it is more likely that they are found later.

  • In standard SQL, explicit joins are the only way to use outer joins:

    SELECT d.Name,
           e.Fname || e.LName AS EmpName
    FROM      Departments AS d
    LEFT JOIN Employees   AS e ON d.ID = e.DepartmentID;
    
  • Explicit joins allow using the USING clause:

    SELECT RecipeID,
           Recipes.Name,
           COUNT(*) AS NumberOfIngredients
    FROM      Recipes
    LEFT JOIN Ingredients USING (RecipeID);
    

    (This requires that both tables use the same column name.
    USING automatically removes the duplicate column from the result, e.g., the join in this query returns a single RecipeID column.)