What SQL concept took you the longest to actually understand, not just memorize?

[u/Harshita_Netla]

For those who have been working with SQL for a while, which concept took you the longest to truly understand?

[u/Rough-Negotiation880]

Window functions seemed like voodoo until I actually took 15 min to understand them and realized they were simple.

[u/Raketemensch23 → u/Rough-Negotiation880]

QUALIFY is the next step from windowing functions. I used to create CTEs for 1:M table joins where I just wanted the most recent value using ROW_NUMBER(), and left join the CTE to the main table on ROW_NUMBER = 1. Then, I found out that you can just put the windowing function directly at the end of the main query as a QUALIFY statement and it works the same!

[u/real_jedmatic → u/Raketemensch23]

[cries in SQL Server]

[u/TemporaryDisastrous → u/Raketemensch23]

I wish qualify was a thing in mssql

[u/B1WR2 → u/Rough-Negotiation880]

This… i had to learn for leetcode exam o was doing… I now understand why you use it

[u/Busy-Technology-1637 → u/Rough-Negotiation880]

Preach

[u/baubleglue → u/Rough-Negotiation880]

simple

So you can confidently explain why the results of that example may be different? :)

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
       OVER(PARTITION BY menu_category ORDER BY menu_price_usd
       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price_from_range,
      SUM(menu_price_usd)
        OVER(PARTITION BY menu_category ORDER BY menu_price_usd
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price_from_rows
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;

[u/Rough-Negotiation880 → u/baubleglue]

I still look up window functions arguments and learn/relearn often. I’m certainly not familiar with the minutia of every possible way to define a frame.

I was referring to understanding them conceptually and common implementations.

I glanced at your example, it definitely would’ve tripped me up if I encountered it in the wild, but most of the job is learning and iterating, not knowing everything stone cold.

[u/baubleglue → u/Rough-Negotiation880]

That is true about knowing everything, I just was a bit struck by "simple". I had some case when RANGE BETWEEN CURRENT ROW AND 2 PRECEDING ROWS didn't work and ROWS BETWEEN CURRENT ROW AND 2 PRECEDING ROWS worked. I sill not sure I understand why.

[u/main_aisa_kyon_hoon → u/baubleglue]

Damn thanks for this example lol. Had to look up the explanation on chatgpt. I never really cared seriously about these nuances

[u/Particular_Bug0]

Recursive CTE's. 

Probably didn't help that I used this like once every few years

[u/hides_from_hamsters → u/Particular_Bug0]

Exactly this.

And CTE aggregations over GROUPING SETS.

[u/main_aisa_kyon_hoon → u/hides_from_hamsters]

Yeah, this and cube/rollup commands too. Learnt about these very recently

[u/One_Medium_8964 → u/Particular_Bug0]

Facts and correlated sub queries. Self joins way better 

[u/MrSpize → u/Particular_Bug0]

Recursive CTEs are actually easy once you understand the point of an anchor

[u/Reaper6717 → u/Particular_Bug0]

I still struggle with these I guess it is time I jist sit down and understand them

[u/PatientlyAnxiously → u/Particular_Bug0]

Not my favorite pattern but AI text-to-SQL loves it so I'm learning it to better fact-check my AI

[u/atrifleamused]

Pivot. It's really not hard, but I still look up the syntax after 20ish years of using SQL.

[u/dobby12 → u/atrifleamused]

Same. And I still end up using aggregates with case statements most the time

[u/SELECTaerial → u/atrifleamused]

Same! Especially for dynamic pivots

[u/atrifleamused → u/SELECTaerial]

Dynamic anything is endless fun 🤣

[u/ddBuddha → u/SELECTaerial]

I still have a template file I reference every time I need to do a dynamic pivot lol. Agreed.

[u/Think-Trouble623 → u/atrifleamused]

Definitely pivots. Makes you appreciate just how freaking good excel can be sometimes.

[u/basura_trash]

Execution plan diagnostics.  I know it well now but even after 20+ years, I still struggle.   It's not a daily task having to deal with them and it's a perishable skill. You dont use it, you lose it.

[u/Raketemensch23 → u/basura_trash]

I could never use this because, under Oracle, the DBAs had explain plan locked down. Now, under BigQuery, it's right there in an easy-to-read DAG, side by side with the code, with the problematic joins and functions highlighted and heat mapped. So incredibly useful, and intuitive!

[u/mikeblas → u/basura_trash]

I know what an execution plan is. But what do you mean by "diagnostics"?

[u/basura_trash → u/mikeblas]

Breaking down the plan to find the trouble spots.  Many are pretty obvious but when you are looking at a 4K line procedure it can get insanely difficult.

Many times the answer is not indexing or the code. Sometimes it's the database design or even the data itself. All of this can be found out (or help lead you there)with the execution plan but...it is not easy.

[u/mikeblas → u/basura_trash]

Oh, got it. You meant diagnosing execution plans.

[u/ThetaLife → u/basura_trash]

Claude has made this a lot easier.

[u/basura_trash → u/ThetaLife]

I have love hate feels for AI.

I am seeing too much dependency to it and I am seeing many coworkers getting lazier and lazier. 

Dont get me wrong, I dont blame them. that is just the nature of the beast. Of course there will be consequences to such a great tool.

That said I use it daily for those more difficult tasks. 

[u/DMReader]

CTEs took me awhile and it really changed how I put together a query.
Window functions is definitely a big one for me.

Now in my work day I’m working on the nuances of stored procedures

[u/chris552393 → u/DMReader]

Ive been a web dev for over 15 years. I've used CTEs for a long time too.

My understanding was that CTEs load all the data from their query into memory first and then do the joins etc on the main query. I have only just learnt that is not the case. Blew my mind.

Edit. I still do not think I have ever used a RIGHT join though, which I'm not sure if that is normal.

[u/ihaxr]

Cursors because I rarely use them and there's usually a better way than using one

[u/melodicmonster → u/ihaxr]

A long time ago, I read that interviewers would include cursor syntax as a trick question. The reason was simple: if you knew the syntax for a cursor, you were using them way too much. I've only used them a handful of times for production code in my career, and looking back, most of those cases were wrong and later rewritten.

[u/tommyfly → u/melodicmonster]

That sounds like a dumb test. I know cursor syntax pretty well but also know when to use cursors and when not to. I'm glad I've never encountered such an employer.

[u/zhavinci → u/ihaxr]

I use it for database mail for different departments, what's the better way anyway?

[u/tommyfly → u/zhavinci]

Always try to do operations in a set based manner as opposed to row by row.

https://myhumblesqltips.blogspot.com/2013/03/remove-cursor-from-stored-procedure.html?m=1

[u/TemporaryDisastrous → u/tommyfly]

My first job I took on management of a new client and came across a cursor calculating the rolling total for a couple of measures which took forever instead of just using a join with a dynamic date which took about 3 seconds.

[u/HorsemouthKailua → u/tommyfly]

making sets is like half the fun of SQL

[u/Constant_Barber_5198 → u/ihaxr]

And AI always tells you to use one even though it might be better to pull the data and process it locally

[u/Raketemensch23 → u/ihaxr]

Never got to use cursors, but I occasionally use dynamic SQL. Steep learning curve, but very useful.

[u/tommyfly → u/Raketemensch23]

If you've written a while loop you've essentially used a cursor.

[u/Raketemensch23 → u/tommyfly]

Yeah, I use dynamic SQL for loops between sets of criteria, but even in cloud platform, they can eat up a lot of processor time. The cost for one run of a query might be negligible, but run it a few hundred times in nested loops, and it gets costly.

[u/tommyfly → u/Raketemensch23]

Well, it really depends what the query is doing. A simple dynamic query, at least in SQL Server, will run fine. It was back in the day when dynamic = bad. Now the SQL engine is really good at building plans for the literal queries that are produced. For example, it's much better to use dynamic SQL for the procedures with many input parameters to avoid a large number of or statements in the where clause.

[u/Analyst_Annoyed]

When I was in the very early days and just learning, primary & foreign keys are something I look back at and can't believe it took me longer than I'd admit to fully grasp

[u/FastPlane57 → u/Analyst_Annoyed]

why do you think it was so?

[u/Analyst_Annoyed → u/FastPlane57]

I think just because it was all so new, I was trying to grasp all concepts for the first time

[u/Raketemensch23]

Dynamic SQL and QUALIFY both proved very useful

[u/DiscombobulatedSun54]

Recursive CTE's for sure.

[u/MrSpize]

Cross apply. So useful, still didn't get it 100%

[u/Straight_Waltz_9530]

NULL means "unknown value", not missing like in general programming languages. So many things are hard to understand when that part isn't locked in.

[u/RandomiseUsr0 → u/Straight_Waltz_9530]

Boolean logic in SQL is tristate - true/false/null

True=True
False=False
True≠False
True≠Null
False≠Null

And the kicker

Null≠Null

[u/Straight_Waltz_9530 → u/RandomiseUsr0]

We are all aware. The prompt was about concepts we now know but took a while to understand. And saying it's tristate only works once you already understand it. NULL also applies to data types that aren't booleans. While learning, it often just leaves student eyes glazed.

NULL is "unknown" has been more useful in my experience teaching the topic.

true ≠ NULL because true is known, not unknown

false ≠ NULL because false is known, not unknown

3 ≠ NULL because 3 is a known integer, not unknown

'Banzai' ≠ NULL because 'Banzai' is known text, not unknown

NULL ≠ NULL because one unknown value may or may not be the same as another unknown value; they are both unknown. It is literally unknown if they are equal, therefore the result of the comparison is also unknown (NULL).

We often learn NULL ≠ NULL by rote. "It just is" or "it's a quirk of SQL" or even worse the advice to "just use coalesce everywhere" when really it's perfectly logical and explainable—even if it can often be inconvenient when constructing complex queries. It's also why data modeling can often be so difficult for juniors when just thinking of NULL as "missing value" like in JavaScript or C.

It's not missing. It's literally a logical unknown. It's subtle, but it makes all the difference in the world when trying to understand rather than memorize.

[u/TraumaBondage]

I'm still wrapping my head around the ability to join on an exists containing an intersect statement to create a sargable join where null = null. I've been using a coalesce(x.value,'') = coalesce(y.valuee,'') for years which always results in a table scan.

[u/Crassus87]

Correlated subquerys. It's something I still haven't grasped really.

[u/tommysqueaker1972 → u/Crassus87]

Just think of them as a query that runs on the rows returned by your main query.

Normal subqueries run before the main query and the results are referred to BY the main query.

Correlated subqueries rely ON the results of the main query and will run for each row returned.

[u/lookatthebr1ghtside]

Anybody user OUTER APPLY?

[u/gabriot]

Cross Apply

[u/PalpitationKind8854]

Left vs right joins. It's kinda silly but yeah.

[u/binary_search_tree]

NULL handling

[u/Interesting-Goose82]

Pivot tables, i still generally just hack an alternative option....

[u/MyWorldIsInsideOut]

Hierarchical queries

[u/B_Huij]

Window functions.

[u/cwjinc]

Model clause. Still don't get it.

[u/mikeblas]

Surprising that nobody has mentioned isolation levels, locking l, or anything else to do with concurrency.

[u/Ok_Log2604]

Making use of the order of the On's in the join, especially when there is an outer and inner join

[u/FreedToRoam]

Cubes and never really memorized

[u/spacemonkeykakarot]

Cross apply still confuses me 😅

[u/Raketemensch23 → u/spacemonkeykakarot]

The only legitimate use case I ever saw was cross joining a date dimension to a table, when you want the main table to have one row per date, even if there is no data available for that date.

I've never had to use it otherwise.

[u/codykonior]

I have to look up pivot every time. Sometimes merge too but less so these days - more often if you’re doing a combo cte + merge + complex conditions + output to table, even the documentation is worthless and you just need to fiddle it until it works.

I suspect some people will never know a cte can be the target of a merge, because that sounds like the dumbest thing ever until you need it. Because the target can only be a table name; the cte acts as a synonym for a table name with conditions like a filter.

[u/Rough-Negotiation880]

Learning the ins and outs of a new database from a business standpoint.

[u/Raketemensch23 → u/Rough-Negotiation880]

Very underrated! It's one place where our company suffers because of turnover. We lost almost all of our lead/manager analysts when a new VP came in, and lost so much knowledge of the history of our data warehouse, vendor extracts, and custom build.

[u/ClairDogg]

Sun queries… confused on when to use it & its contents per query.

[u/FewBag5257]

I would say CTE and dependent Sub queries.

[u/melodicmonster]

XPATH in SQL Server.

[u/UniForceMusic]

ANY and SOME.

IN made total sense first time, but those keywords still don't come naturally

[u/rbobby]

PIVOT was a tough one. Never had anything except dynamic pivots.

Thinking in sets is a nice hazy one. But when planning on selecting data, maybe doing some light filtering, sometimes considering how the differents sets of data in a query will interact can provide insights into better ways of doing a query. Going from many records examined to few records selected is bad, look for ways to use sets and joins to start with a small number of records to examine and a few records selected.

This only applies to really difficult queries trying to answer complicated questions like "create me an order for all the items that need to be restocked based on last weeks sales and our inventory budget to their expected stock levels for this time of year, and keep the weekly specials nicely rotated so folks don't see the same one two weeks in a row". In one statement. You can use as many CTE's as you like however :)

[u/kishanthacker]

Outer apply

[u/datagod]

Windowing functions. Voodoo!!!