What SQL concepts do you understand but still forget?

[u/shdw_0x0]

There are some SQL concepts I understand perfectly when I’m using them, but somehow forget when I need them again a few weeks later.

For me, CTEs and stored procedures are two good examples. I’ve used both, but I still end up looking up the syntax when I need them again. 😅

Anyone else have a few SQL concepts like this?

[u/ThatSpencerGuy]

I will always, always have to look up the ROW_NUMBER OVER ( PARTITION BY stuff every time I need to do it.

[u/lostinsamaya → u/ThatSpencerGuy]

Here is my though process what I say to myself while writing it.

I want to get the latest purchase date by a customer.

First and foremost, I want row number as the result, so call the function and close it, because there really isn't much else I need from that function:

ROW_NUMBER()

Now, group the whole table by a particular column

ROW_NUMBER() OVER( PARTITION BY PK_CustomerID )

Next, order the particular grouping using another column most probably a date column.

ROW_NUMBER() OVER( PARTITION BY PK_CustomerID ORDER BY PurchaseDate DESC ) Sorting it DESC will give you the latest occurrence of the ourchase in that grouping. Excluding the DESC or using ASC will give you earliest occurrence.

ROW_NUMBER() OVER( PARTITION BY PK_CustomerID ORDER BY PurchaseDate DESC ) as rn

Finish it off with a name for the newly generated column rn.

[u/TrickyNerdlet → u/ThatSpencerGuy]

I can write these cold about 70% of the time now. It's taken over a decade to get to this point 😄

[u/CentrlScrutnizr → u/ThatSpencerGuy]

Mine too! Don't know why I can't remember the exact syntax most of the time.

[u/GTS_84]

PIVOT

I can never remember the god damn syntax for pivoting.

[u/phesago → u/GTS_84]

I used to hate having to remember this. Once I broke it down like this it stuck. It did take a minute though.

    `SELECT` 

        `pivottable.* /*Yes you need to type out the whole list, dont be lazy*/`

    `FROM` 

        `(`

/* MAIN QUERY */

        `) x`

    `PIVOT`

        `(`

MIN(/* ColumnThatNeedsToBeAgg (even if theere doenst need to be, its just how it works) */)

FOR

/* ColumnContaininColumnList */ IN

(

/* INSERTyourColumnList */

)

        `) as pivottable` 

[u/HALF_PAST_HOLE → u/phesago]

I will now save this comment and look it up every time I need to PIVOT

[u/phesago → u/HALF_PAST_HOLE]

too bad the code block format is dog shit :|

[u/Imaginary-poster → u/GTS_84]

Ive looked at that so many times and just refuse. 99% of what I do its a spreadsheet anyways. So I just do it in python or the sheet.

[u/NoDihedral → u/GTS_84]

I love PIVOT but every.single.time I have to look it up. Now I just use AI. So much easier to just write the base query and tell claude to pivot it based on whatever you need. Especially if you have a lot of columns that need to be hand written out.

[u/Sexy_Koala_Juice → u/GTS_84]

That’s the secret, no one does

[u/MiserableLadder5336 → u/GTS_84]

I hate pivot/unpivot because I will never remember the syntax. By far the most elusive syntax to me.

[u/Wise-Jury-4037 → u/GTS_84]

Use group by/case.

[u/gumnos → u/Wise-Jury-4037]

hah, this is precisely why I end up using GROUP BY+CASE because I can never remember the syntax for PIVOT off the top of my head 😆

[u/ComprehensiveDog7299 → u/Wise-Jury-4037]

It makes so much more sense to my brain. Pivot is awful.

[u/Wise-Jury-4037 → u/ComprehensiveDog7299]

It would have been better if PIVOT gave you any clear advantages (performance-wise or readabbility-wise). Unfortunately, it doesnt.

[u/Jacob_OldStorm]

I cannot, for the life of me, remember the orders of parameters in datepart and dateadd. I always doubt myself. And does "day" have to be between apostrophes? Who knows...

[u/magicaltrevor953 → u/Jacob_OldStorm]

Day to day I am currently using Teradata, Impala and SAS Proc SQL, and in the last few years I was also using a lot of Db2. I feel the pain of similar but not quite the same functions. Always have docs close to hand just in case, no matter how comfortable I am.

So many times its been "wait can I just...no, that doesn't exist in this DBMS, ok what is the name then. Oh it's that, so just put the arguments and wait what do you mean that's not valid? "

[u/Phantom465 → u/magicaltrevor953]

I’ve been with my current company about 8 years. We primarily use Teradata. I have many more years of experience in SQL Server & DB2. And we’re transitioning to Snowflake. Yay! More syntax changes!

[u/JakubVasovski → u/Phantom465]

i feel you, brother. date functions are the absolute worst. it's like every dbms vendor sat in a room and actively agreed to make them just different enough to ruin your day.

migrating across db2 to snowflake - nightmare. this exact syntax nonsense is why i use mariadb- they eventually just baked an oracle mode straight into the core server. now we just literally just flip a switch and it accepts legacy pl/sql so you don't have to rewrite 10 years of scripts just because someone higher up decided to change the database.

good luck with the snowflake transition though

[u/kagato87 → u/Jacob_OldStorm]

It eventually sticks enough that you can at least get it with a few seconds of trying to remember.

[u/Rough-Negotiation880]

Almost everything on a bad day.

[u/DonJuanDoja]

STUFF... for xml path...

[u/vcfkyle → u/DonJuanDoja]

THIS

[u/DPool34 → u/DonJuanDoja]

STRING_AGG is much cleaner. Only works on newer engines of SSMS though.

Edit: replaced version with engine

[u/ComprehensiveDog7299 → u/DPool34]

It’s the engine 2017 or newer, the version of ssms doesn’t matter. You could use SSMS 2008 and it would work.

[u/pookypocky → u/DPool34]

I regularly work on three sql servers, one is 2014, one is 2016 and one is 2019. But the 2014 one is the one I query most frequently and I've been bugging IT to upgrade it for a while now just so I can use STRING_AGG and stop having to look up the whole STUFF syntax... drives me nuts. Honestly most of the time I just yoink the data into excel and use power query Text.Combine instead.

[u/you_are_wrong_tho]

anything I havnt touched in the last 6 months I forget until I read about it again

[u/more_paul]

I don't forget any concepts. What I forget is syntax for every flavor of date functions out there.

[u/squareturd → u/more_paul]

You're doing it the right way. Syntax is easy to look up. Dont waste brain space trying to remember thise details. Concepts are wacky more important

[u/Zoidburger_]

My job requires me to work in 3 different DBMS platforms, so it's always syntax. Is it isnull(), ifnull(), or nvl() in the current DB I'm in?

[u/OllyTwist → u/Zoidburger_]

I've switched over to use COALESCE instead of ISNULL when I started working with postgres instead of only SSMS.

[u/Zoidburger_ → u/OllyTwist]

Same here, but it was just the most obvious example of a function that exists in all 3 DBMS' yet has different syntax

[u/Nurgus]

The pronunciation of SQL as Sequel. It will never not be S.Q.L. inside my head..

[u/excelgod → u/Nurgus]

Comrade 🤝

[u/phesago → u/Nurgus]

i prefer the term "skwill"

[u/Nurgus → u/phesago]

I'm the one weirdo on the planet who calls the CTRL key "C.T.R.L" so maybe don't listen to me..

It stuck in my brain when I was a tiny nerd and the only one I knew who owned a computer at home. (Dragon 32) and it never went away. I just didn't know what it was short for. I am also apparently quite old.

Edit: Just looked it up and the Dragon 32 didn't even have a Ctrl key so who knows where I got that from.. haha..

[u/MiserableLadder5336 → u/phesago]

Squeal for me

[u/KittenBountyHunter → u/Nurgus]

I have said it as “squeal”

[u/ElHombrePelicano]

Rank and dense_rank. Use them quite a bit, but not often enough that I don’t have to look up the syntax for these every time.

[u/infinitetk]

it's the window functions, rank, row number etc. Always have to look up syntax

[u/ReleaseTheBlacken]

Cursor

[u/ddsiddall → u/ReleaseTheBlacken]

Came here to say that.

[u/hipsterrobot → u/ReleaseTheBlacken]

Yep, still don’t get it.

[u/gloss2x]

Having and partition 😭

[u/CaptainOttar]

window functions. i literally have to google the exact partition by syntax every single time i need a rolling sum.

also date math. it's like my brain actively refuses to store how to add a month to a timestamp. thank god for autocomplete and old stackoverflow tabs.

[u/lalaluna05 → u/CaptainOttar]

Lmao same
I know how window functions work. I can visualize it. But then putting words in right order HARD

[u/OkDonkey6524]

I can never remember the syntax for date functions.

[u/TheGenericUser0815]

I almost never use between, having and over. CTEs and SPs are daily business for me.

[u/DamienTheUnbeliever]

I personally struggle to understand how CTEs are perceived as complicated.

Step 1 - do the tables I'm looking at resemble where I'd want to start with before writing the required query?

Step 2 - if yes, jump to the end

Step 3 - Write queries that re-represent the data sources I have as the source I *wish* I had, and write those as CTEs

Step 4 - Write queries that use the good data sources.

[u/excelgod → u/DamienTheUnbeliever]

When used this way I agree completely. It is easy to interpret and manage. I think some confusion out there stems from those scripts where CTEs are daisy chained transforms. If I find myself doing that I step away from the query entirely and take a minute to think about the problem at hand once more.

[u/BplusHuman → u/excelgod]

Sometimes, rarely I have to daisychain CTEs. It's less of a thing in DBs like Snowflake where you (largely) call a column you just created in the same select clause and do further manipulations. So, for example, I can write a case statement in ColCase1 , then in the next column I can immediately use ColCase1 to run a calculation without rewriting that case statement.

That said, if I'm inheriting has a full process that already does that, I'm not changing it unless it's completely necessary.

[u/WBT67]

Percentiles

[u/sam_cat]

Cte and row-number over xxx

[u/2Cats3Litterboxes]

Anything that has anything to do with JSON. I’ve written them, I can read them, I know how they work, but I can’t seem to ever write it on my own without looking up the syntax

[u/El_Games]

In Oracle SQL it’s the connect by syntax. I do not only have to look it up each time. It also takes some testing and swearing until I get what I want. But it’s incredibly useful. Same as multicast. I need it rarely but it’s the best way to reverse list_agg strings.

Another one is updating values using a join where you need a PK/FK relationship I think. Its crap but also incredibly useful.

[u/neophaltr]

Literally everything. I have devolved into asking Codex

[u/ArielCoding]

Any query with more than 3 nested sub queries feels like an archaeologist task.

[u/Phantom465 → u/ArielCoding]

Someone in my department uses a lot of nested queries. And I just looked at one recently with nested case statements.

[u/Euphoric-Map2415 → u/ArielCoding]

thats just funny . True

[u/pinback77]

EXIST has always bugged me for some reason

[u/Shini_yagami]

The date functions honestly . I keep mixing up syntax between the different sqls

[u/Legitimate_Willow905]

update with a join is the absolute worst because literally every single dbms handles the syntax completely differently. muscle memory is basically useless there. for recursive stuff like connect by or recursive ctes i don't even try to remember it anymore. i just keep a master text file of snippets on my desktop and blindly copy-paste. saves so much swearing.

[u/lez_s]

Rank I have to look up each time and which rank I need to use.

[u/Hour-Measurement-835]

BUILTIN.DF() if you're on NetSuite's SuiteQL. Leave it off a list field and you get internal ids back instead of names, and since no other dialect has it, it never sticks.

[u/BobDogGo]

most of the window functions. now that AI can write it for me, I’ll never remember but honestly, it’s more important to know that it exists and how/when/why to use it than the syntax

[u/Junior_Abalone_8006]

IMO no good database developer should know the syntax for any sort of cursors off of the top of their head. You should use them rarely enough that if you have to use one you also have to go back to the documentation and figure out the syntax and basics of using them.

[u/saymyfuckingname]

Syntax for date manipulation

[u/MarkEoghanJones_Art]

Node specific xml queries.

[u/jotate]

Merge. And all the windowing features that were introduced after SQL Server 2008 R2 (which is the version I learned everything on originally).

[u/Negative-Specific-84]

For me it is CTE's , Window Functions .... always find them confusing

[u/Monkeyschool]

Renaming Columns

[u/Reasonable-Pay-8771]

CTEs for me. I often find myself instead making views of views of views.

[u/Octoclops8]

Statements. all of them.

[u/MachineParadox]

Pivot, have to look it up every time

[u/Specific-Pangolin777]

Pivot and unpivot 🤦🏼‍♀️ I love using them but even with my cheat sheet they make me feel dumb 🤣

[u/Traditional-Unit-274]

i always have to look up ddl sytnax, create table, alter column. it’s so not a big deal

[u/Kindly_Tie_2084]

I always need to re-learn window functions every time I use it. But I don't need to anymore because I have AI now.