What's your favorite construct in SQL?

3 years ago by megaman1970 to c/programming

For me, it's CTE's. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.

What construct do you like using on a daily basis?

deegeese 5 points 3 years ago

Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.

save

path: 0 111578, hotness: undefined, score: 5, children: 1

luciole 3 points 3 years ago

I believe recursive CTEs are pretty cool for tree traversal. Anytime you've got a table with a foreign key on its own primary key they might come in handy.

save

path: 0 111578 116040, hotness: undefined, score: 3, children: 0

DarkDarkHouse 5 points 3 years ago

CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.

save

path: 0 111545, hotness: undefined, score: 5, children: 1

DinosaurSr 1 point 3 years ago

Same here, CTEs were a game changer for me when I learned about them

save

path: 0 111545 188044, hotness: undefined, score: 1, children: 0

szeis4cookie 3 points 3 years ago

I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it's just nicer and easier to read the intermediate step than as a subquery

save

path: 0 111210, hotness: undefined, score: 3, children: 0

DarkAngelofMusic 3 points 3 years ago

CTEs can be useful, particularly in PostgreSQL, where there are writable CTEs, but a lot of the time, I prefer using temp tables over CTEs, as they often perform better for larger datasets. I think one of my favorite constructs is window functions. I've found many uses for them, over the years.

save

path: 0 114640, hotness: undefined, score: 3, children: 1

megaman1970 1 point 3 years ago

There was one time a good while back where I used window functions to perform edge detection in a dataset. I'll see if I can dig up that query later.

save

path: 0 114640 115716, hotness: undefined, score: 1, children: 0

key 3 points 3 years ago

Subqueries in Subqueries in Subqueries

save

path: 0 111464, hotness: undefined, score: 3, children: 1

megaman1970 1 point 3 years ago

Those work, but require a lot of careful structuring to get right, and can be a pain to debug. With a CTE, you can just call on the intermediate steps to trace down problems.

save

path: 0 111464 114146, hotness: undefined, score: 1, children: 0

gam3 3 points 3 years ago

https://en.wikipedia.org/...

save

path: 0 120113, hotness: undefined, score: 3, children: 0

lwhjp 2 points 3 years ago

Yes, CTEs are awesome. Especially when you don't force materialization and the optimizer can work its magic.

I've had a lot of fun with window functions as well.

save

path: 0 116125, hotness: undefined, score: 2, children: 0

itty53 1 point 3 years ago

You can create a functional enum view by just assigning enums as the column names and storing a single row of the int (or whatever enum) representation.

Then use that view in a cross join. You can (almost) eliminate magic numbers entirely and makes the code much more human legible.

save

path: 0 114089, hotness: undefined, score: 1, children: 0

TitanLaGrange 1 point 3 years ago

I find the MERGE statement to be extremely useful.

Window functions I really want to like, but I don't have enough uses for them to learn them well enough to feel really comfortable with them.

save

path: 0 383426, hotness: undefined, score: 1, children: 0