Guidelines for non-ORM-generated SQL code.
The rules below do not apply when a SQL statement expression is generated automatically by an ORM; however, in explicitly specified SQL statements, they do apply.
Do not use the old-style form, where the join condition is specified only in the WHERE clause, without the use of a JOIN clause.
Bad
SELECT
a.article_id,
a.title,
up.name
FROM
article AS a,
user_profile AS up
WHERE
up.user_id = a.author_id;
Good
SELECT
a.article_id,
a.title,
up.name
FROM
article AS a
JOIN user_profile AS up
ON up.user_id = a.author_id;
Do not use correlated subqueries in place of JOIN. For example, the following is equivalent to the example statements in point 1, but is not acceptable, since there is a JOIN equivalent (as seen above):
Bad
SELECT
a.article_id,
a.title,
(
SELECT
up.name
FROM
user_profile AS up
WHERE
up.user_id = a.author_id
) AS name
FROM
article AS a;
Note: The use of a correlated subquery as a participant in a JOIN, rather than instead of a JOIN, is permitted, but should be avoided when possible.
Table/view aliases must be used in multi-table FROM clauses—that is, in all join expressions—unless the table name is 5 characters or less in length.
The AS keyword must be used when declaring an alias.
Table/view aliases should be constructed from initialisms for the correlated table or view name. For example, an alias for a table named user_profile would be named up, while an alias for building_location_detail would be bld.
If a given table is included 2 or more times in a join expression, all of its aliases must be numbered—e.g., bld1, bld2. (Underscores may be used between the initialism and the number: bld_1, bld_2; as usual, consistency is strongly advised.)
A column alias must be used whenever an item in a query select list is a computed expression, rather than a simple column name.
The AS keyword must be used when declaring a column alias.