Best Practices to Write Clean SQL Queries With Examples
Advanced SQL Interviews don't just test you on correctness – they often check out your SQL coding style too, especially during take-home SQL challenges.
Usually, they are checking to see if you follow SQL coding conventions that result in readable and maintainable SQL queries. That's why in this tutorial, we're going to teach you these 8 SQL query best practices:
- Uppercase for Keywords
- Lowercase or Snake Case for Names
- Descriptive and Concise Aliases
- Consistent Formatting and Indentation
- Avoid Writing SELECT *
- Use JOINs Explicitly for Clarity
- Format Dates Consistently
- Comment Wisely
Uppercase for Keywords
Let’s start with the ✨basic✨: Use uppercase for the SQL keywords and functions. It makes your queries clearer.
Avoid writing it like this:
Instead, opt for this format:
Lowercase or Snake Case for Names
When it comes to naming things like schemas, tables, and columns, go for lowercase or snake case (using underscores) - it's a widely adopted convention in programming languages!
For example, instead of:
Choose this format:
💡 Our Suggestions:
- While some prefer variations to distinguish schemas, tables, and columns, adhering to snake case is recommended.
- Remember, consistency matters, so consult your team's conventions for optimal harmony.
Descriptive and Concise Aliases
Make your queries easy to read by using short and meaningful names for tables, columns, and expressions, and avoid the need for lengthy or ambiguous names.
Avoid this approach:
Instead, use this technique:
💡 SQL Writing 101:
- Use the "AS" keyword when assigning aliases to enhance visibility for new column or table names.
- For calculated columns, choose meaningful names that reflects their purpose to ensure newcomes to the query can easily understand what's being calculated.
Consistent Formatting and Indentation
Want your queries to be easier on the eyes? Keep your formatting consistent. Use spaces or tabs throughout, and things will look neater.
For example, avoid this:
Instead, write like this:
💡 A little tip: You can also use a code formatter tool to automatically apply a style to your code.
Avoid Writing SELECT *
Don't use in your queries. Instead, explicitly list the columns you need. This improves query performance and makes the query more readable.
Don't write like this:
Instead, dothis:
💡 SQL Writing 101: If you're new to a table and want to see every column, add a clause to reduce the number of rows being generated:
Use JOINs and Aliases for Clarity
Make your queries more readable by using simple names and combining data from different tables with s.
Avoid doing it like this:
Instead, opt for this format:
Specify JOIN Types
Be clear about your JOIN types (, , , etc.) to improve query clarity.
Instead of being vague like this:
Specify the JOIN type:
Format Dates Consistently
Use a consistent date format to prevent ambiguity.
Avoid this:
Instead, use a consistent format (assuming 'YYYY-MM-DD' format):
Comment Wisely
Use comments to explain your queries, but avoid writing long ones between queries. Briefly explain each step for clarity. You can use to write single-line comments in SQL.
Here's an example of not-so-helpful comment:
Instead, write a useful comment:
💡 A tip to remember: Use for longer comments and make sure they add valuable insights.
By following these tips, your queries will be easier to understand, maintain, and work with!
What's Next: SQL Pivoting & Un-Pivoting
In the next tutorial, we'll change gears, and cover how to pivot and unpivot in SQL (similar to how PIOVT works in Excel). Have no clue what we're dtalking about? No problem!
Next Lesson
EXECUTION ORDER ↕️