SQL Clean Code in 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 singleRecipeIDcolumn.)
