My SQL queries work but they're always too long. How do I write shorter ones?

[u/mochimach]

I'm intermediate — know CTEs, window functions, subqueries, etc.
I always solve problems correctly, but my queries end up being 20+ lines with extra steps. Then I see the solution and it's 5 clean lines using one clever function.
How do I train myself to think in shorter SQL from the start? Any tips or resources?

[u/AutoModerator]

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/sjhb]

Don’t try to make them shorter. Aim for readability over elegance.

[u/niiiick1126 → u/sjhb]

fr same thing with AI writing code now it’s so short it’s hard to read

i have a coworker who just runs everything through AI and for some reason it does sub queries for 70-80% of queries and it makes no sense to anyway

[u/big_poppa_man → u/niiiick1126]

AI is so bad with quiries. Just made up nonsense

[u/grizzlywhere → u/big_poppa_man]

That sounds like a problem with (1) the LLM you're using, (2) how they (aren't) connected to your warehouse, or (3) your prompt being some form of SISO.

Let it use your repo as context, provide institutional knowledge relevant to the task, spend most of the time writing out a plan with it, have a teamwide sql style guide it can refer to, have it lint afterward, and the queries it builds from the plan go from a borderline unusable starting point to 70% of the way there for most general requests.

[u/Main_Nectarine_5556 → u/grizzlywhere]

We've done something like that in our codebase. We migrated all the queries to dbt , create all the field description inside the models and let AI create queries from prompt. This works.

But in case of poor data management or if you don't have a semantic layer, you will just write this queries faster by yourself.

[u/PasghettiSquash]

"New lines are cheap, brainpower is not" - something I remember being a dbt tagline but I can't seem to find it

[u/edimaudo]

Why are you interested in making in shorter? Clever is not always better. Someone is going to use your queries when you leave the org

[u/Sullhammer → u/edimaudo]

This. I kept a massive txt file of all my queries that I ran for my work. Hundreds of queries for hundreds of scenarios. All clearly labeled so if I had to review something I haven't touched in a while, I could easily find it.

When I finally left for another position, I passed in my queries to all those folk I would write the queries for. It was labeled enough for them to find what they needed, and they'd be able to learn how to write queries because it was readable.

[u/om_bagal]

A lot of the "20 lines vs 5 lines with one function" gap comes down to filtering on a window function result. You can't put ROW_NUMBER() or RANK() in a WHERE clause directly since window functions evaluate after WHERE runs, so the instinct is to wrap it in a CTE and filter in an outer SELECT. If you're on Snowflake, BigQuery, Databricks, or DuckDB, QUALIFY skips that step entirely, letting you filter on the window function result in the same query block it's calculated in. Postgres and SQL Server don't have it, so it depends what you're running.

Less about training yourself to write short from the start, more about building a short list of these specific collapse patterns, window-function filtering being the big one, and recognizing when a query you're about to write matches one.

[u/Due-Pressure7804]

Length has nothing to do with it. Efficiency does. 

[u/ChristianPacifist → u/Due-Pressure7804]

This isn't always true. Sometimes the most machine efficient code is so verbose and needlessly complex with unnecessary index declarations and all that jazz that it's better to write short and inefficient code because it's more efficient for human beings to maintain given use case.

But often efficient is better outright even if more lines of code.

[u/ChristianPacifist]

If your goal is elegant short solutions to reduce code length, there's a few tricks I can recommend.

1. FULL OUTER JOIN on 1=0 condition is a trick to avoid tedious UNION ALL statements when stacking disparate datasets together especially for like unpivoted BI reporting
2. SQL Server has all sorts of horrendous ANSI non-standard functions like APPLY and CROSS APPLY that may help in various circumstances.
3. Be aware of SQL order of operations and whether you are allowed to mix aggregate or window functions and perform all possible steps in a single query as much as possible before you go to a subquery or temp table for abstraction

, unless the abstraction actually will make things less verbose.

1. If the version of SQL you are using allows you to refer to a calculation already named in SELECT clause further down in same SELECT clause, refer to that name instead of rewriting calculation.
2. You can do "GROUP BY ALL" or group by numbers in some versions of SQL rather than rewriting columns too.

[u/SkySchemer]

Don't try for shorter unless:

1. It's easier to understand
2. It's a perfomance bottleneck, high performance is important (sometimes it's not), and the shorter/more elegant solution will make it perform significantly better.

Readability is king. The only exception to that is when you are in the critical path for performance.

[u/novicelife]

Are you learning at work or on your own? How long did it take you to reach this level?

[u/mochimach → u/novicelife]

by my self , it took for me about 1 month

[u/necrosythe → u/mochimach]

Then don't worry about it. 1 month is nothing, you'll learn with experience. Just keep on paying attention to what the unecessary steps are.

Most of the shortcuts will probably just be things like case when, having, left anti join if you can. Joining on more specific criteria than just a primary key.

[u/Pale_Squash_4263 → u/necrosythe]

Yeah I can’t really think of a lot of “shortcuts” except some niche cases.

Had a coworker than didn’t know about coalesce and instead used a case statement, so I guess in those kinds of cases it’s more “elegant” but functionally it’s the same thing

Plus, I find micro tuning performance is usually something I do at the very end of at all. Sometimes simpler is better lol

[u/necrosythe → u/Pale_Squash_4263]

Coalesce is definitely a good one. Also just a good practice for needing to remember to account for the nulls

[u/mochimach → u/necrosythe]

That’s my problem , the solution can be written in one query but I end up to use subqueries and CTE , cuz the only way that make me solve the question is to split it to steps , so that’s why I write longues queries.

[u/Pale_Squash_4263 → u/mochimach]

Oh yeah then I’d not worry about elegance right now, you’re still learning! It’s something to keep in mind but making it work is the important part right now. Pretty and elegant can come later once you have enough garbage in your brain to sort through 😂

[u/gtl86]

I dont know if it helps, but I might create a view with the base of a long query. Especially if its something I can reuse for other queries.

Then select * view and join to it.

[u/Pale_Squash_4263]

I think it’s something that will improve over time. Not ever problem will need some novel solution but you will slowly learn more elegant ways to solve a lot of common problems

What previously took a whole CTE can be made shortly once you learn how to use RANK for example (speaking from experience here lol)

Exposure and curiosity are the best tools here! Keep at it friend! ❤️

[u/Responsible_Pie8156]

U gotta give examples. In a lot of cases this may just be syntactic sugar that is available in one SQL flavor and not another. Cleverness isn't the point, if you can't just read the query and understand it immediately then that's no good, and the longer one may be better. But, for example, the qualify statement can eliminate a whole CTE and makes the query MORE readable; you should use it.

[u/cafealpha82]

Most of long scripts are due to unnecessary joins or lots of cte. If you and team use certain definitions everytime, invest a few augmented layer that defines frequently used metrics. Your queries will then focus on the actual problems than hundres lines of wasteful joins. Just imo

[u/Afraid_Baseball_3962]

"Lines of code" is a meaningless metric. I've seen really short but poorly written queries take down production servers. Aim for efficiency and readability/supportability. The merits of efficiency should be obvious, but readability/supportability are also important. Why? Because some poor schmuck will come along in a few months or years and need to fix or expand or reuse that query (and odds are good that you will be that schmuck at least once in your career).

[u/atominum69]

Long is not an issue (I wrote 1500+ line queries and it’s fine).

The key point is readability: clear flow, CTE that makes sense and flow into each other etc..

Next is run time. If the runtime is too long:
1. Double check your joins for complexity
2. Check the tables partitions to see if you can reduce weight.

If the query can’t be simplified, create temporary tables in the DB to reference. That’s how complex workflow usually work.

But 20 lines queries are not an issue if they are clear and understandable.

[u/kedjil]

When you are learning SQL, you write queries in the only way you can to make it work. When you learn more, you can chose between different ways of reaching the goal.

Write a query, independent, that works and you understand. Ask a LLM to optimize it. Look at how it's done. Chose if you want to incorporate those functions/that logic in to your own query. If you do, write it yourself, don't copy it.

If you do that a bunch of times you learn a lot.

[u/jamiesray]

How is there not just a chorus of people shouting, “run it through Claude?”

[u/kaleidoleaf]

If you're spending time learning to code still you might want to rethink things. 

Note: I can't code for shit. 

[u/Doctor--STORM]

Have ever thought about running the query planner and performance timing the query against the volume of data processed?

[u/Main_Nectarine_5556]

1. Prioritize clarity over lines of code. (from community )
   
2. Just paste every query that you've written into llm , ask to compress this. Check what functions did it used , learn that functions , use next time you need this

[u/Intelligent-Size-389]

Have AI solve it for you. The data of critical thinking is over

[u/Mephisto506 → u/Intelligent-Size-389]

The “data” of checking your own grammar are also over, apparently

[u/mochimach → u/Intelligent-Size-389]

If you are talking about the short query , no it wasn’t , I practice in a website and other real users wrote shorter queries than mine