Mike McKee

Here’s the harsh truth about writing SQL code…

You will spend the majority of your time debugging and testing code.

Happy? I hope so because it’s the truth. Unless you’re a one-of-a-kind SQL wizard who writes flawless scripts, you’ll spend a lot of time stressing out while debugging and testing your masterpieces.

But I have good news for you…

There are ways to make the debugging/testing process less stressful. You can optimize and prettify your SQL code to make it:

Easier for you to read
Easier for your coworkers to read
Easier for you to manipulate and comment out

In this post, I’ll show you why the following four SQL formatting tricks help me debug and test code faster and how they will help you do the same:

Use lowercase instead of uppercase
Use separate lines for each select field
Use “where 1=1”
Use leading commas

To make my explanations more clear, I have a nifty little idea…

We’ll go through my formatting tips one-by-one to transform the below query from a hideous eye pain to something so sexy you’ll want to ask it on a date by the end of this post.

Our original "messy" query that we'll prettify by the end of this post

And in case you’re wondering what this query does, it accesses a fictional table containing (too much) personal data about all the data dorks in the country.

Lucky for you, it’s not real. But the tricks are.

So, let’s get started with tip number one…

Use lowercase instead of uppercase for keywords

Starting off this post HOT. I’m hitting you with a bit of a controversial SQL topic. This is a very trivial debate that turns many friends into enemies - uppercase or lowercase letters for SQL keywords.

But I’m here to tell you that what you choose doesn’t matter.

Some say uppercase letters help you distinguish keywords like SELECT, FROM, WHERE, etc, from other parts of the query.

Although I don’t buy that.

We live in the 21st century. In the year two thousand and freaking twenty-three. I don’t care if you use MySQL, SQL Server, PostgreSQL, or another editor.

You don’t type in black and white.

Most SQL editors highlight SQL keywords in different colors. They even help you distinguish functions (like sum, count, avg) from the keywords.

So why do I use lowercase letters? Because they are easier to type. Every time, I save myself a second or two by not holding down shift or clicking caps lock. I know it doesn’t take much time to hit those keys, but it disrupts my momentum when I’m in a flow state typing.

So, for me, the first step of our query prettification is to make our query look like this:

Changing uppercase into lowercase (personal preference)

Before moving on to the next tip, I’ll acknowledge the existence of IntelliSense. Most editors have this feature that detects what you’re likely to type next and autofinishes the word for you. Some people claim you can use this for SQL keywords, but none of the editors I use have that IntelliSense feature. So, reach out to me on LinkedIn if you know something I don’t.

Anyway, on to the next tip…

Use separate lines for each select field

Look at our query’s current state. It’s ugly how everything in the select statement is so damn cramped. I mean, give your queries some room to breathe. We aren’t ruthless data analysts.

We respect our queries and our eyes.

Here’s a fun test I thought of to prove that piling every field in your select statement onto one line is a bad idea…

Quick! Within a second, tell me how many columns that query will output. I guarantee you can’t count that fast. But when we clean up our query to look like what you see below, suddenly, you can tell me how many columns it will output by simply looking at the final field's line number.

Giving each field in select statement its own line for better readability

Your eyes will instantly thank you…

And if you work with a data team, your boss and coworkers probably look at your code, too - especially if you’re creating and altering views or stored procedures that live in the database. So do their eyes a favor and let your queries breathe.

Don’t choke it down.

Give each field its own line.

Use “where 1=1”

I learned this next trick on the first day of my data analyst job. While getting all comfy in my sexy, sparkly office (aka my cubicle), I hopped on some calls with my coworker and watched him work on some SQL.

My coworker constantly wrote “1=1” at the beginning of his where clauses.

Why? I didn’t know at first. It seemed odd because one always equals one…

And that’s the point.

Since one always equals one, the statement is always true. So, while the query executes, the where clause checks if 1=1 holds for every row, which it always does.

But why does this trick matter? What does it really do?

It prevents you from constantly deleting parts of your where clause while debugging code.

Here’s an example…

Imagine you want to alter our query so it searches for data dorks who have a “Data Analyst” job title and more than one year of experience, but how far away they are from you doesn’t matter for now. You’ll add the filter back later but don’t want to see it now.

Well, what do you do?

With our current query, you’d have to delete the “DistanceAway < 10 and “ phrase from line 10.

But here’s the problem…

When you want to add the filter again, you’d have to retype the phrase. Yeah, it’s not a big deal with this short phrase. But what happens when you’re debugging and testing a query with 10+ filters in the where clause?

That’s some unnecessary deleting and typing.

Instead, we use the 1=1 as a placeholder to comment out parts of the where clause easily.

Using "where 1=1" to make commenting out lines easier

If you wanted to, you could comment out lines 12 and 13 of our query to test it without any filters.

Use leading commas

This one’s similar to the “where 1=1” tip. Using leading commas makes commenting out fields in your select statement easier.

Looking at our current query, we have 8 fields outputting when we run it. But remember, we’re just testing right now. We don’t need everything. Say we don’t care about JobTitleId, Salary, and YearsExperience for now. Again, we’ll add them back later but don’t want to include them in this test.

Thanks to the separate lines in the where clause, we can easily comment out the “YearsExperience > 1” part.

And we can easily comment out our three fields. But this is where we run into a major problem.

The DorkId field on line 5 now ends with a comma - meaning we’ll run into an error if we run the query. We can delete the comma and add it back in later, but that’s annoying over time. When working with big queries containing more than 500 lines, it’s easy to forget about those commas we delete.

That’s why leading commas are so powerful.

They give you a query looking like this:

Using leading commas to make commenting out lines easier

Voila! A miracle, right?

This is another trick I never knew until I started my data analyst job. And I use this every single time I’m writing a query. Yeah, some people say it may look ugly or unnatural this way, but that’s life. Not all of our queries can look like supermodels in bikinis.

It’s the personality that counts.

And leading commas tend to have a more flexible, forgiving personality than trailing commas.

Quick Warning

Before finishing off this post, let me remind you that these tips are all about personal preference. If you think leading commas look ugly or where 1=1 is useless, then don’t listen to me.

I don’t care.

But I do care about time. And I know you always say the following about life: “I wish I had more time to do ___”

These four tips will help you shave a few minutes every time you go to debug or test code. You may not think it’s a lot of time, but it will add up over the course of weeks, months, and years.

They won’t make your code more efficient or faster. But they will open up more time for you to do whatever ___ matters to you.

We're Almost Done

But first, here’s how our final query looks…

Pretty, right?

The Dorky TLDR

The writer in me loves a good old tldr at the end of a blog post.

It serves two purposes:

Summarizes everything you read in the post
Acts as a cheat code for those who like skimming through articles

If you’re part of the latter, don’t feel bad. I’m one of you too.

So here’s the tldr for this post. Aka the four SQL formatting tips that help me debug and test code faster:

Use lowercase instead of uppercase for keywords
Use separate lines for each select field
Use “where 1=1”
Use leading commas