CASE CONVENTIONS
For SQL development, we will use the following case conventions:
All Caps
SQL Key Works (SELECT, FROM, WHERE, etc.)
Lower Case
Database names
Schema names
Proc Names
Table names
Field names
Aliases
Table definitions
Camel Case
Parameters
Variables
FORMATTING
- Use a line break and consistent indent after query key works SELECT, FROM, WHERE, GROUP, HAVING
- Each field/table in a SELECT, WHERE or JOIN statement should be on a separate line if there are more than ~5 fields and/or the SELECT statement is not in a subquery.
- Join fields should always be in the ON clause, not in the WHERE clause. If there are more than two join fields, put them on separate rows with the proper indenting
- Use meaningful (and short) aliases after all table references and do not repeat aliases in same block of code
- Always use 1=1 after the key word WHERE and add all conditions using AND
- SELECT statements (and all other lists) should have commas at the beginning of the field names
- In subqueries, use discretion on line breaks and spacing. These should remain compact but readable (see IF NOT EXISTS clause above).
Example
SELECT
stu.field1
,stu.field2
,dt.field1
FROM
full_student_table stu
INNER JOIN full_course_table crse ON st.key = crse.key AND st.key2 = crse.key2 AND st.key3 = crse.key3
INNER JOIN full_date_table dt ON stu.key = dt.key AND stu.field1 = dt.field1
WHERE 1=1
AND tab.field1 != test
AND crse.key IS NOT NULL
