sql best practices

list of recommended do’s & don’ts

Etiquette

  • Start the SQL document with a block code introduction

  • Use in-line comments to explain the ‘not-so-obvious’ of your queries

  • Even if the ORDER BY statement defaults to ASC, write it out to make your intentions clear

  • Use aliases for aggregated columns

  • Use CAPS for functions and LOWERCASE for names or variables

  • Use names that are interpretable; e.g., tax = 0.1475 instead of x = 0.1475

  • When using sub-queries, include an in-line comment explaining what it does

  • You can create white space to make the code easier to read, or you can click on the “Beautify” paintbrush icon (red arrow in screenshot)

  • When assigning an alias, if the word turns blue (in MySQL), it means it is a reserved keyword

  • For aggregations, use abbreviations that are easily interpretable; e.g. pct_revenue, tot_spent

https://www.sqlstyle.guide/

https://gist.github.com/fredbenenson/7bb92718e19138c20591

Best Practice with New Databases

  • To default/highlight the schema, run the USE statement; USE <schema>

  • Next step is to look around, so run SHOW TABLES

    • Limit it to a few rows in case the table has thousands of records that would cause a slow execution