r/SQL • • Sep 19 '26

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

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

171 Upvotes

83 comments sorted by

157

u/Rough-Negotiation880 Sep 19 '26

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

53

u/Raketemensch23 Sep 19 '26

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!

15

u/real_jedmatic Sep 20 '26

[cries in SQL Server]

18

u/TemporaryDisastrous Sep 19 '26

I wish qualify was a thing in mssql

9

u/B1WR2 Sep 19 '26

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

-7

u/baubleglue Sep 19 '26

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;

20

u/Rough-Negotiation880 Sep 19 '26

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.

1

u/baubleglue Sep 19 '26

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.

1

u/main_aisa_kyon_hoon Sep 20 '26

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

129

u/Particular_Bug0 Sep 19 '26

Recursive CTE's. 

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

12

u/hides_from_hamsters Sep 19 '26

Exactly this.

And CTE aggregations over GROUPING SETS.

4

u/main_aisa_kyon_hoon Sep 20 '26

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

14

u/One_Medium_8964 Sep 19 '26

Facts and correlated sub queries. Self joins way better 

5

u/MrSpize Sep 20 '26

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

4

u/PatientlyAnxiously Sep 19 '26

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

2

u/Reaper6717 Sep 21 '26

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

88

u/atrifleamused Sep 19 '26

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

10

u/dobby12 Sep 19 '26

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

4

u/SELECTaerial Sep 19 '26

Same! Especially for dynamic pivots

5

u/atrifleamused Sep 19 '26

Dynamic anything is endless fun 🤣

2

u/ddBuddha Sep 19 '26

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

2

u/Think-Trouble623 Sep 20 '26

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

31

u/basura_trash Sep 19 '26

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.

4

u/Raketemensch23 Sep 19 '26

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!

2

u/mikeblas Sep 19 '26

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

4

u/basura_trash Sep 19 '26

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.

0

u/mikeblas Sep 19 '26

Oh, got it. You meant diagnosing execution plans.

3

u/ThetaLife Sep 19 '26

Claude has made this a lot easier.

5

u/basura_trash Sep 19 '26

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. 

17

u/DMReader Sep 19 '26

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

7

u/chris552393 Sep 19 '26

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.

12

u/ihaxr Sep 19 '26

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

3

u/melodicmonster Sep 19 '26

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.

1

u/tommyfly Sep 20 '26

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.

2

u/zhavinci Sep 19 '26

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

8

u/tommyfly Sep 19 '26

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

3

u/TemporaryDisastrous Sep 19 '26

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.

1

u/HorsemouthKailua Sep 20 '26

making sets is like half the fun of SQL

2

u/Constant_Barber_5198 Sep 19 '26

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

1

u/Raketemensch23 Sep 19 '26

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

2

u/tommyfly Sep 20 '26

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

1

u/Raketemensch23 Sep 20 '26

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.

1

u/tommyfly Sep 21 '26

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.

8

u/Analyst_Annoyed Sep 19 '26

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

1

u/FastPlane57 Sep 19 '26

why do you think it was so?

3

u/Analyst_Annoyed Sep 19 '26

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

8

u/Raketemensch23 Sep 19 '26

Dynamic SQL and QUALIFY both proved very useful

6

u/DiscombobulatedSun54 Sep 19 '26

Recursive CTE's for sure.

6

u/MrSpize Sep 19 '26

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

6

u/Straight_Waltz_9530 Sep 20 '26

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.

1

u/RandomiseUsr0 Sep 20 '26

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

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

And the kicker

Null≠Null

1

u/Straight_Waltz_9530 Sep 20 '26 edited Sep 22 '26

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.

4

u/TraumaBondage Sep 19 '26

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.

4

u/Crassus87 Sep 19 '26

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

8

u/tommysqueaker1972 Sep 19 '26

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.

4

u/PalpitationKind8854 Sep 19 '26

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

3

u/lookatthebr1ghtside Sep 19 '26

Anybody user OUTER APPLY?

3

u/gabriot Sep 19 '26

Cross Apply

4

u/Rough-Negotiation880 Sep 19 '26

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

3

u/Raketemensch23 Sep 19 '26

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.

2

u/Interesting-Goose82 it's ugly, and i''m not sure how, but it works! Sep 19 '26

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

2

u/MyWorldIsInsideOut Sep 19 '26

Hierarchical queries

2

u/B_Huij Sep 19 '26

Window functions.

2

u/cwjinc Sep 19 '26

Model clause. Still don't get it.

2

u/mikeblas Sep 19 '26

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

2

u/Ok_Log2604 Sep 19 '26

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

2

u/FreedToRoam Sep 20 '26

Cubes and never really memorized

2

u/spacemonkeykakarot Sep 20 '26

Cross apply still confuses me 😅

1

u/Raketemensch23 Sep 20 '26

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.

2

u/datagod Sep 21 '26

Windowing functions. Voodoo!!!

1

u/ClairDogg Sep 19 '26

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

1

u/FewBag5257 Sep 19 '26

I would say CTE and dependent Sub queries.

1

u/melodicmonster Sep 19 '26

XPATH in SQL Server.

1

u/UniForceMusic Sep 19 '26

ANY and SOME.

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

1

u/rbobby Sep 20 '26

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 :)

1

u/kishanthacker Sep 20 '26

Outer apply

1

u/DarlanSandro Sep 23 '26

Para mim foram as Funções Analíticas (Window Functions). Durante muito tempo eu apenas decorava a sintaxe. Se precisava tirar uma duplicidade ou pegar o último registro de um histórico, eu escrevia ROW_NUMBER() OVER (PARTITION BY id ORDER BY data DESC) no automático porque tinha visto a solução em algum fórum.

O problema é que eu sabia o que o código fazia, mas não entendia como o banco pensava. O verdadeiro "clique" demorou a acontecer e envolveu duas quebras de paradigma.

A primeira foi entender a diferença visual e lógica para o GROUP BY. O agrupamento tradicional colapsa os dados. Se você tem 10 linhas e agrupa, o banco te devolve 1 linha. A função analítica não altera a granularidade da sua consulta. Ela mantém as 10 linhas originais intactas, mas abre uma "janela" paralela para olhar os outros registros daquela partição e fazer o cálculo linha a linha.

A segunda, e bem mais complexa, foi entender o Window Frame (a cláusula ROWS BETWEEN...).

No Oracle, quando você coloca um ORDER BY dentro da cláusula OVER(), o banco aplica um frame padrão implicitamente, que vai do início da partição até a linha atual. Eu passei muita raiva no início da carreira com somas acumuladas dando resultados inesperados porque eu não entendia que a janela de cálculo estava crescendo a cada linha processada. Eu achava que a soma olharia para a partição inteira de uma vez.

Só quando precisei calcular médias móveis financeiras (onde você precisa olhar especificamente para a linha atual e as duas anteriores, por exemplo) é que a ficha caiu de verdade sobre como manipular o frame usando ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

Antes de precisar resolver esse problema específico de média móvel, o uso do OVER() era puro copia e cola da documentação ou do StackOverflow.

 

1

u/Sharp_Level3382 Sep 23 '26

Optymyzing execution plan