

SQL

Query Writing

1. Keywords and functions MUST be uppercase (SELECT, WHERE, MAX, AND).
2. Column aliases MUST be lower snake_case.

3. When the SELECT, WHERE, GROUP BY or ORDER clauses contain more than one element, they MUST be on a new line after the keyword, indented once (one indent = 4 spaces):

   SELECT
       first_name,
       last_name
   FROM users
   WHERE
       username = 'sxybill9'
       AND email <> 'bill@microsoft.com'
   

4. When a SELECT, WHERE, GROUP BY or ORDER clause only contains one element, it MAY all be written on the same line for brevity:

   SELECT
       first_name,
       last_name
   FROM users
   WHERE id = 321142
   

5. When a statement is written within a string in PHP:

   1. The query MUST start on a new line, indented once.
   2. A semicolon MUST NOT be added at the end of the statement.

6. All joins MUST be written like:

   INNER JOIN table_name
       ON condition1
       AND condition2
   

   Joins MUST NOT use the unqualified JOIN keyword.

7. Functions MAY be split across multiple lines, where each subsequent line is indented once. When doing so, the first item in the list MUST be on the next line, and there MUST be only one argument per line.

   SELECT
       CONCAT(
           first_name,
           ' ',
           last_name
       ) full_name
   FROM users
   

8. CASE statements MAY either be written using the previous rule or on a single line:

   • split lines

     SELECT
         CASE
             WHEN gender = 'M' THEN 'male'
             WHEN gender = 'F' THEN 'female'
             ELSE 'not provided'
         END
     

   • concisely

     SELECT
         CASE WHEN gender = 'M' THEN 'male' ELSE 'female' END
     FROM users
     

9. Subqueries MUST be started on the next line and indented once; the opening brace MUST be on the same line as the FROM clause, and the closing brace MUST be on its own line along with the subquery alias:

   SELECT
       my_subquery.*
   FROM (
       SELECT
           prenom first_name,
           COUNT(*) count_names
       FROM users
       GROUP BY prenom
   ) my_subquery