Formatting should follow the SQL Style Guide

[AmrElsayyad]

Formatting should follow the SQL Style Guide

For example, spaces should be used to line up the code so that the root keywords all end on the same character boundary. This forms a river down the middle making it easy for the reader's eye to scan over the code and separate the keywords from the implementation detail.

The expected output:

SELECT *
  FROM people
 WHERE people.phone_number =
       (SELECT phone_calls.receiver
          FROM bakery_security_logs
             , atm_transactions
             , bank_accounts
             , flights
             , passengers
             , phone_calls
             , people
         WHERE bakery_security_logs.license_plate = people.license_plate
               AND atm_transactions.account_number = bank_accounts.account_number
               AND bank_accounts.person_id = people.id
               AND flights.id = passengers.flight_id
               AND passengers.passport_number = people.passport_number
               AND phone_calls.caller = people.phone_number
               AND bakery_security_logs.activity LIKE 'exit'
               AND bakery_security_logs.year = 2021
               AND bakery_security_logs.month = 7
               AND bakery_security_logs.day = 28
               AND bakery_security_logs.hour = 10
               AND bakery_security_logs.minute BETWEEN 15 AND 25
               AND atm_transactions.atm_location LIKE 'Leggett Street'
               AND atm_transactions.transaction_type LIKE 'withdraw'
               AND atm_transactions.year = 2021
               AND atm_transactions.month = 7
               AND atm_transactions.day = 28
               AND flights.id =
                   (SELECT flights.id
                      FROM flights
                         , airports
                     WHERE flights.origin_airport_id = airports.id
                           AND airports.city LIKE 'Fiftyville'
                           AND year = 2021
                           AND month = 7
                           AND day = 29
                     ORDER BY hour
                         , minute
                     LIMIT 1)
               AND phone_calls.duration < 60
               AND phone_calls.year = 2021
               AND phone_calls.month = 7
               AND phone_calls.day = 28);

The actual output:

SELECT *
FROM people
WHERE people.phone_number = (
            SELECT phone_calls.receiver
            FROM bakery_security_logs,
                  atm_transactions,
                  bank_accounts,
                  flights,
                  passengers,
                  phone_calls,
                  people
            WHERE bakery_security_logs.license_plate = people.license_plate
                  AND atm_transactions.account_number = bank_accounts.account_number
                  AND bank_accounts.person_id = people.id
                  AND flights.id = passengers.flight_id
                  AND passengers.passport_number = people.passport_number
                  AND phone_calls.caller = people.phone_number
                  AND bakery_security_logs.activity LIKE 'exit'
                  AND bakery_security_logs.year = 2021
                  AND bakery_security_logs.month = 7
                  AND bakery_security_logs.day = 28
                  AND bakery_security_logs.hour = 10
                  AND bakery_security_logs.minute BETWEEN 15 AND 25
                  AND atm_transactions.atm_location LIKE 'Leggett Street'
                  AND atm_transactions.transaction_type LIKE 'withdraw'
                  AND atm_transactions.year = 2021
                  AND atm_transactions.month = 7
                  AND atm_transactions.day = 28
                  AND flights.id = (
                        SELECT flights.id
                        FROM flights,
                              airports
                        WHERE flights.origin_airport_id = airports.id
                              AND airports.city LIKE 'Fiftyville'
                              AND year = 2021
                              AND month = 7
                              AND day = 29
                        ORDER BY hour,
                              minute
                        LIMIT 1
                  )
                  AND phone_calls.duration < 60
                  AND phone_calls.year = 2021
                  AND phone_calls.month = 7
                  AND phone_calls.day = 28
      );

Notes on what to change:

1. Root keywords should have the same ending point instead of the same starting point.

   -- This:
   SELECT *
   FROM people
   WHERE people.phone_number =

   -- Instead of this:
   SELECT *
   FROM people
   WHERE people.phone_number = (

2. New lines following opening parentheses, commas, etc., should be indented to start on the right side of the river, the same as the previous line.

   -- This
   WHERE people.phone_number =
      (SELECT phone_calls.receiver
         FROM bakery_security_logs
            , atm_transactions

   -- Instead of this:
   WHERE people.phone_number = (
           SELECT phone_calls.receiver
           FROM bakery_security_logs,
                 atm_transactions,

3. Opening parentheses should be moved to the beginning of the new line instead of the end of the previous line.

   -- This:
   WHERE people.phone_number =
      (SELECT phone_calls.receiver

   -- Instead of this:
   WHERE people.phone_number = (
           SELECT phone_calls.receiver

4. Closing parenthesis should be on the end of the current line instead of being on a new line.

   -- This:
                    ORDER BY hour
                        , minute
                    LIMIT 1)

   -- Instead of this:
                       ORDER BY hour,
                             minute
                       LIMIT 1
                 )

5. Put the commas at the beginning of the line instead of the end. This will make debugging for missing commas so much easier. (added by the request of @monomeric)

   -- This
         FROM bakery_security_logs
            , atm_transactions
            , bank_accounts
            , flights
            , passengers
            , phone_calls
            , people

   -- Instead of this
           FROM bakery_security_logs,
                 atm_transactions,
                 bank_accounts,
                 flights,
                 passengers,
                 phone_calls,
                 people

You're welcome to point out any additional notes or discuss this further.

[monomeric]

Enabling comma first notation would also be a treat!

[AmrElsayyad]

Enabling comma first notation would also be a treat!

Great addition @monomeric! It would make debugging for missing commas so much easier.