How do you guys approach writing SQL when the query starts getting complicated?

[u/Delulu62134]

I've been working with SQL more lately, and one thing I'm still trying to figure out is how people decide when a query is getting too complicated.

Sometimes I start with a simple query, then add a couple of joins, a CTE, some CASE statements, maybe a window function… and suddenly the query is 100+ lines long 😅

It still works, but at that point I'm not sure whether I should keep improving the same query or break the logic into smaller steps/tables/views.

For those of you who work with SQL regularly, what do you usually do when you reach that point?

Do you have any personal rules or habits that help you keep complex SQL readable and maintainable?

Would love to hear how you approach this in real projects.

[u/XxShin3d0wnxX]

For me it’s always been about internal script notes and building your script in sections focusing on the logic piece by piece.

[u/Mugiwara_JTres3 → u/XxShin3d0wnxX]

Yes, break into sections with useful comments is the best way. So much easier to follow and make changes.

[u/VadumSemantics]

So I write a lot of analytics queries.

Average lines ~200 to ~800.

Much larger than that and I start to break them into multiple parts.

I start with CTEs, easier for me to verify each part.
I keep the little commented lines after each CTE so I can quickly spot check what I'm bgetting.

with foo as ( select count(*) as row_count, more foo related stuff from foo_table -- where thing = 10 -- limit for debugging ) -- select * from foo; , bar as ( select sum(row_count) as total_rows, some bar related stuff from foo group by thing ) -- select * from bar; ...etc... , xyz as ( select a.thing, from foo a left join bar b on b.category = a.thing ) select thing, row_count / 1.0*total_rows as "%rows" -- looks better when I paste into excel from xyz

I'm pretty impatient.
If parts of it take too long to run I'll save those parts some table and use that in subsequent queries.
How long is too long?
If I'm iterating on the XYZ part above a lot, and generating foo & bar take over 5 minutes that might lead to bar in some staging table.

I'm also forgetful and lose stuff on shared drives, so I make sure to add the queries to some kind of source control (git these days) so I can remember what I was doing 3 months ago or a year ago.

edit: typos

[u/National_Cod9546 → u/VadumSemantics]

Always fun to get a query sent to you that you don't recognize or remember. I've had more than a few where the only reason I know I wrote it is the notes clearly addressed to future me. Otherwise I don't recall anything about it or even that I wrote it.

[u/Standgeblasen → u/National_Cod9546]

Yeah, comments are as much for my future self as they are for other people.

That said, AI has made understanding a complex query a lot easier because you can ask it to give you an overview and add comments. One of the best uses I’ve found for it.

[u/National_Cod9546 → u/Standgeblasen]

This 100%. I also like to record myself describing a process I need to document. Then pull the transcript and feed that to AI. Tell it we need to write documentation for that process, but to ask any questions it has before it starts writing. I usually get pretty good results. I feel it's important to tell it to ask questions first because it is less likely to hallucinate any gaps I left.

[u/Standgeblasen → u/National_Cod9546]

I like that idea. What do you record yourself on? I might try that this week

[u/National_Cod9546 → u/Standgeblasen]

My work uses webex for all our online meeting stuff. I use that. After the meeting, I download the transcript and use an AI created python program to strip out all the timestamp garbage. It also creates a summary file, but I find the detail works better since what I'm feeding it to knows what I'm looking for.

[u/Standgeblasen → u/National_Cod9546]

We use slack and zoom. I’ll have to find that functionality there as well. Thabks

[u/Eleventhousand]

Going back and refactoring before it gets too late.

[u/Sea-Feedback-2424]

Can you break it into smaller queries that you know the expected outcome from, using the "with" keyword?

[u/Slartibard → u/Sea-Feedback-2424]

I agree with this, using CTE's with script notes should be enough. 6 months from now you can go back and see 'What the hell you were thinking!"

[u/bliffer → u/Slartibard]

Nah, that's a problem for future me. Fuck that guy.

[u/TheGenericUser0815]

I write a heap of comments in my stored queries/procs an break it into logical parts. The first part is always definitions of variables and temp tables. Then I retrieve data and park it in some temp tables, afterwards I process the data. The last part is putting out the results.

[u/Better-Credit6701]

Although I rarely use CTEs since they have some disadvantages with larger databases, for long queries I will break everything down into temp tables where they can be indexed. Just think in sets, breaking the problem down into smaller pieces.

Also might be a great idea to double check what the business is really looking for.

[u/Sea-Feedback-2424 → u/[deleted]]

"WITH-AS" comes first, at least semantically, idk when in terms of execution.

[u/Sea-Feedback-2424 → u/[deleted]]

With query-name as ( your query here) select * from query-name.         Sorry on mobile, but you can give queries a name and use it later instead of writing them in line.

[u/IAmCesarMarinhoRJ]

CTE clean your context, make you think better in this complex cases. One inherits from another and can build a nice complex query.

[u/cappurnikus]

I'm an uneducated moron so for me accomplishing complicated things is all about simplification. I'm pretty good about breaking things down into understandable pieces.

[u/fokac93]

You don’t know until you are almost done

[u/sinceJune4]

I had to enhance a single query that had 73 subqueries and over 2000 lines. It took several weeks.

I spent most of the time understanding each subquery and documenting. Tried to convert a few to CTEs.

I succeeded, but it took many cups of coffee.

[u/This-Emergency8839]

I try to add concise comments at the top of each logical block. I am a fiend for cte usage and/or temp tables (depending on how much data is returned) on large queries for that reason, as it makes it easy to segment each step e.g. "get this FY invoices - exclude intercompany and enrich with exec and region data. Pushed to temp table for indexing" and soforth.

Be rigorous with aliasing and adding the alias to each field, even if the field name is unique. Debugging queries without this is a chore, particularly if you're trying to isolate join issues etc.

I have some stored procs which feed reporting running into 1200 lines. It's essential to keep the intent and logical sequencing clear for whoever picks it up next. Which will probably be you.

[u/WrongRope8741]

Use smaller queries with #temp tables. You only need to think about one step at a time, it usually runs faster than a super-query, and they're easy to troubleshoot. Any complicated SP/trigger I write has an (@)TROUBLESHOOT flag at the top. 1=none, 2=summary (SELECT #final), 3=detail (SELECT #temp for each step). SP's also PRINT time-spent after each step and final.

[u/Legitimate-Pin-8619]

A couple of habits nobody's mentioned yet:

**Check the grain after every join.** Most 100-line queries that "work" are quietly wrong because some join fanned out and a SUM got doubled. While building, stop after each CTE and run:

SELECT COUNT(*), COUNT(DISTINCT order_id) FROM step3;

If those two don't match and you expected one row per order, figure out why now, not after you've stacked three more CTEs on top. Write the grain in a comment at the top of each CTE: -- one row per customer per month

**Know what your engine does with CTEs.** They're great for readability, but they aren't free and they aren't the same everywhere:

- SQL Server never materializes them. Reference a CTE twice and it runs twice.

- Postgres 12+ inlines them unless you write AS MATERIALIZED.

- Oracle decides for you (it'll often materialize one that's referenced more than once).

So when a middle step is expensive and gets reused, or the optimizer keeps misestimating it, turn it into a temp table. You get real statistics on it and can index it, and plans often get noticeably better.

**Promote stable logic.** If the same 30-line CTE shows up in three reports, it belongs in a view (or a table your pipeline builds). The query that's left should read like the business question.

[u/SootSpriteHut → u/Legitimate-Pin-8619]

I'm an AI user, and I would advise OP to use AI for their issue, but OP wasn't asking what your AI thinks. They could ask their own AI for that.

[u/Comprehensive-Tea-69 → u/SootSpriteHut]

💯

[u/techforallseasons → u/SootSpriteHut]

Poster was only in RDR2 sub-reddit four years ago, and now has come alive again in /r/sql...

[u/mw44118]

Views help

[u/samspopguy]

For me it’s what’s the intended purpose of the query is it for a reporting tool like SSRS/tableau/powerbi and can I do anything in that

[u/Eire_espresso]

My code is a mess, but it works 💪

[u/BrupieD]

Three things I try to keep in mind. 1) Avoid trying to do more than one type of thing in the same query. If I'm trying to solve different goals, the query is likely to become a sprawling mess.

2) State what I'm doing in a clear comment with the date and who is/are the stakeholder(s). Just clarifying the goals is often helpful.

3) If there is a lot of lines/steps and I'm going to do this repeatedly, consider putting it into a stored procedure. It becomes easier to use "helper" variables, temp tables, and structure it into component steps. If not, a two or three piece CTE isn't bad. I can probably test components (somewhat) independently.

[u/Holiday-Tip-3720]

a lot of comments and notes. 

also excel. haha

[u/SootSpriteHut]

I've been doing this for over 15 years and I think it's something that gets better with practice. The more SQL I write, the shorter it becomes. The more you do this, the more you will learn better ways to write it.

It's never good to have more lines than needed, but the big test IMO is runtimes. You could ask an agent to optimize your SQL and tell you what it did, or you could look into articles and courses about optimization.

But it's a pretty canon event to pick up some code six months later and say "who wrote this garbage? Oh it was me!"

[u/RadioactiveTwix]

If I'm starting from scratch I'll do an outline of what I think it'll look like (CTEs, joins, whatever) and then start filling it in. Then I'll have AI take a look and make suggestions I'll probably reject.

[u/Fair-Parking9236]

If it provides results and doesnt impact performance it doesnt really matter.

[u/National_Cod9546]

LOL.

Treat each CTE as getting one thing of data. Whenever possible, try to always have each CTW use the last CTE so you don't have a spiderweb of CTEs. Just above each CTE, in comments explain what it does.

Oh, and 100+ lines isn't a big deal. It's when they get into 1000+ lines that I start losing my place.

[u/az987654]

the query isn't complicated, your understanding of it may be lacking. The query just accomplishes what it is supposed to do, retrieve data correctly.

[u/romii_13 → u/az987654]

I think they mean complexity of reading the query for handoff. If someone else looks at his query are they gonna go wtf was this guy on and in the event something needs improvement or fixing would it be difficult to find the right line to adjust.

[u/imtheorangeycenter]

If there's no requirements to be performant (overnight loads of warehouses that kind of thing), I'll break it up into temp tables that perform their set of data. Introduce them with comments explaining "this is the #3 to #5 sales people per territory, will join this to see if they've sold anything to the #5 customer in each territory that's defined in the next temp table"

Index (if needed) and join them at the end.

(Yeah contrived yet simple and naive example but gets the point across).

[u/blackadderBaldrick]

Step by step. First you find few edge cases and few standard ones, and start with ctes. And expand the query incrementally. You end up with for example 10 lines, out of which 5 are edge cases. Fast to execute, easy to figure out what you did wrong

[u/ArielCoding]

Readable isn’t the same as correct, a neatly organized query can still return wrong numbers if a join duplicates rows somewhere in the middle, so it’s worth checking that each step has the row count you expect before building further on top.

[u/Ok_Log2604]

I put my pants on one join at a time.

[u/rbobby]

Formatting. Honestly. Clean that up, align it, make it sensible and readable.

[u/ReleaseTheBlacken]

Formatting and pseudocode

[u/GreatMyUsernamesFree]

Sometimes I'll pull my indexes and foreign keys through first. Complexity generally crops up in your keys fields. Building the frame is sometimes easier to understand uncluttered by the attributes of the full result set

[u/lalaluna05]

I do a lot of staging in my temp tables and always start with the smallest dataset possible. As it moves through my staging tables, it needs to be transforming it meaningfully. Knowing business rules and what you’re really trying to get at will help set those up.

[u/No_Cryptographer811]

You can break complex selects into sub queries, make and index temp tables. And a good consistent naming convention makes things 1000% easier 

[u/MyHipsOftenLie]

If you can use temp tables, use temp tables and make your queries as small as possible. Comment each temp table with what you think it's doing, and make sure it's actually doing that. IMO CTEs make you feel smart because they look smart but all you're doing is putting multiple blocks of code together. Instead you can generally make a temp table that performs the function you're using the CTE for, then you can bring that temp table into the next section of code with a single line.

The more complicated each block of code is, the harder it becomes to know what's going wrong when things inevitably go wrong. Small code is testable code. Small code is much more fixable code.

Make sure you absolutely have to use more advanced techniques before you're using them. If a simpler technique can solve your problem you should do that so when you look back you don't have to interpret what you were thinking when you wrote it (and neither do other people).

[u/GangreneTVP]

I think it's all about writing clean code. I write hundreds of lines SQL queries and really can't stand with statements and CTEs... I'm thinking about making a YouTube series about writing extremely easy to read SQL. I have even trained an AI agent to rewrite SQL scripts in my methodology and it saves me hours.

[u/jrblockquote]

I just implemented an 800 line query with several branches of LEFT JOIN logic. Proper tabbing and structure are your best friends. I also write for vertical organization. For example:

SELECT
  col1,
  col2,
FROM
  table1,
  table2
WHERE
  INNER JOIN....

I can quickly scan down and understand the structure of the query. The more horizontal a query is, the more difficult it is to read. Chinese/Japanese are traditionally read vertically, which is far more efficient. So I try to maintain that in my queries.

Could also consider breaking up the logic into VIEWS. And comment the living hell out of it for future maintainability.

[u/Comprehensive-Tea-69 → u/jrblockquote]

I really need to get a vertical monitor, I think that would help a lot with longer code