Discussion on: Comments Are The Only "Code Smell"

To be clear, I don't mean to imply that I have some awesome magical way of writing queries that makes the hairiest queries easy to understand for even the most junior of devs. But this is an example of how I write them:

SELECT
   person.firstName,
   person.lastName,
   permission.accessLevel
FROM
   person
LEFT JOIN
   permission ON permission.personId = person.personId
WHERE
   person.personId IN (<someListOfPersonIds>)
AND
   permission.isDefault = true
ORDER BY
   person.lastName,
   person.firstName

That may not look "revolutionary", but there are a few key points to this format:

• Every item in the return list is on its own separate line.
• Every field is fully qualified. (Nothing is simply referenced as firstName or accessLevel. It's always referenced as person.firstName or permission.accessLevel.
• In the case of a SELECT, the order is always SELECT followed by FROM followed by any-and-all JOINs, followed by any-and-all filters, followed by ORDER BY (if needed).
• Keywords are always capitalized.
• In the case where the tables/columns are not clearly named, I always alias them into easier-to-read names, like this:

SELECT
   person.firstName,
   person.lastName,
   permission.accessLevel
FROM
   prsn AS person
LEFT JOIN
   perm AS permission ON permission.personId = person.personId
WHERE
   person.personId IN (<someListOfPersonIds>)
AND
   permission.isDefault = true
ORDER BY
   person.lastName,
   person.firstName

Also, I never alias them into one-or-two-letter abbreviations. I hate this:

SELECT
   p1.firstName,
   p1.lastName,
   p2.accessLevel
FROM
   person AS p1
LEFT JOIN
   permission AS p2 ON p2.personId = p1.personId
WHERE
   p1.personId IN (<someListOfPersonIds>)
AND
   p2.isDefault = true
ORDER BY
   p1.lastName,
   p1.firstName