SQL Syntax Best Practices: How to Structure Your SQL Code

SQL is not just about writing queries, you also need to make sure that your queries are performant, fast, and readable. This video shows you the best practices for structuring SQL queries. Even when your SQL code works correctly, it can still be improved, in particular when it comes to performance and readability.

______________________________________________________________________

______________________________________________________________________

Timeline:

Intro: (0:00​​​)
Example Question: (0:40​)
Initial Solution to Refactor: (2:51)
Remove Multiple Nested Queries: (3:30)
Ensure Consistent Aliases: (7:12)
Remove Unnecessary ORDER BY Clauses: (10:32)
Remove Unnecessary Subqueries and CTEs: (11:18)
HAVING vs WHERE: (12:48)
Formatting: (14:23)
Conclusion: (15:36​​​)
______________________________________________________________________

About The Platform:

______________________________________________________________________

Contact:

If you have any questions, comments, or feedback, please leave them here!
______________________________________________________________________

#StructureYourSQLCode

[chaitanyas]

🎯 Key Takeaways for quick navigation:

00:00 *📝 Importance of Code Structure*
- Understanding the importance of code structure for comprehension and logic,
- Exploring a real SQL interview question to demonstrate code structuring,
- Highlighting the significance of well-structured code in job interviews and professional work environments.
02:41 *🛠️ Refactoring Nested Queries*
- Refactoring nested queries for clarity and improved logic flow,
- Demonstrating the use of common table expressions (CTEs) for better code organization,
- Emphasizing the importance of understanding and manipulating data before applying additional logic.
07:14 *🔄 Ensuring Consistent Aliases*
- Utilizing consistent and descriptive aliases for tables and columns,
- Improving code readability and comprehension through clear naming conventions,
- Highlighting the significance of explicitness in code for future reference and collaboration.
10:42 *📊 Eliminating Unnecessary ORDER BY Clauses*
- Streamlining code by reducing redundant ORDER BY clauses,
- Optimizing code execution for efficiency and performance,
- Emphasizing the importance of minimizing unnecessary code execution for code optimization.
12:47 *🧹 Simplifying Code with CASE Statements*
- Simplifying code logic using CASE statements for concise and efficient code,
- Demonstrating optimization by reducing the number of common table expressions (CTEs),
- Streamlining code structure for improved readability and maintenance.
13:02 *🔍 Choosing Between HAVING and WHERE Clauses*
- Differentiating between HAVING and WHERE clauses based on usage and optimization,
- Opting for WHERE clauses over HAVING clauses for simpler and faster query execution,
- Highlighting the importance of understanding SQL nuances for code optimization.

[agnespitka]

Thanks. I learned a lot. I'm pretty new to the industry. I am working with SQL every day so videos that start with "what is SQL" and "this is a SELECT statement" are really not what I need but I am not in the level to write good/optimal code. The video was exactly what I needed. And not too long and not too short. Everything structured well.

[CruiserPup]

I've learned so much from your videos and my stratascratch premium subscription in the past year. Thank you thank you thank you! :)

[Sanatos]

I love your videos! Hope you get the recognition you deserve brother

[StanleySI]

Hi Nate, are you sure we can use where clause after group by clause ? [HAVING vs WHERE: (12:48)]

[weiyang]

thank you for your video, but WHERE should be put before the GROUP BY clause, right?

[israelgonzalez]

In love with your videos. I am enjoying them a lot. Greetings from MX! 😀

[waskjohnson]

Thank you as this helped me think differently around optimization, and that it's more intuitive than big and scary.

At work I review a lot of other's queries (internal & client) and code readability is *always* an issue. Drives me insane as I always have to rewrite/reformat them before I even bother trying to understand them.

[ahyoungkim]

i love this contents, this is so useful to look back our codes! thank you so much Nate !

[sagarmgandhi]

I think I could apply all this when I write my queries. Good session

[jennajia]

Hi Nate, I cannot explain how much appreciation I have to you. Your video is extremely helpful and I have almost watch every single SQL one. All of the questions you explain in a clear way and the platform you built out is also the best. You are my best SQL teacher I want to say. Thank you so much. Keep up making these, we love it.

[bien.papachin]

great video but as a a rule of thumb run the SQL to see if it works, for example the 'where' clause at the last select was left after the 'group by', nonetheless the explanation is great! thanks for the video... cheers

[luckychitundu]

Hi @ Nate!
Does StrataScratch have a shortcut for formatting your SQL code?

[insider-training]

Very helpful video Nate, thanks for doing this.

[totleariss]

Love this video! I’m at the point where I can get through a lot of the problems, but the code is a mess and way longer than the optimized solution. It’s been a challenge to “think in SQL” to write a better code to begin with.

[dlqrhmt]

Do we really need “group by date, n_nonpaying, n_paying” in the last query? cte returns data already grouped by date: one row - one date.
PS. Surprised that it’s on Hard level. Seems pretty basic.

[amitbarak]

I dont understand why group by is necesery in the last select query

[bocanegradev]

amazing! always it's better to understand best practices with examples like this... It would be awesome to see more like this

[elleandish]

nate!!! i was looking up sql videos and thought i saw a familiar face from high school 😆 nice channel, very helpful! i’m not doing data science by title but wanted to get better at sql. 💖 janelle

[alfatmiuzma]

Loved this video❤️. Everything is on point! I really liked the optimization techniques using WITH, CASE statements! Looking forward to more videos on SQL. Thanks a ton😊