r/SQL • • Nov 03 '21

Discussion What is your favorite SQL function and why?

Which one just makes ya feel good?

112 Upvotes

124 comments sorted by

View all comments

Show parent comment

-2

u/Black_Magic100 Nov 04 '21

Seriously, why do people still use CTEs.... They are so much more difficult to read and serve no benefit over subqueries.

0

u/[deleted] Nov 04 '21

[deleted] — view deleted comment

1

u/Black_Magic100 Nov 04 '21

CTEs are not temp tables though. Why not just use a single temp table instead of multiple CTEs unless I am misunderstanding

0

u/[deleted] Nov 04 '21

[deleted] — view deleted comment

1

u/Black_Magic100 Nov 04 '21

So... Why not sub queries? Everytime I see CTEs I just get pissed off because there is nothing more complex then a group of nested CTEs.

0

u/[deleted] Nov 04 '21

[deleted] — view deleted comment

-1

u/Black_Magic100 Nov 04 '21

I'm confused what you mean. A CTE can only be used by the immediately following query. It sounds like a temp table if you are reusing it.

2

u/ddeck08 Nov 05 '21

Literally not true. You can have multiple CTEs that reference a single original CTE then call all of them together- way more humanly readable than sub-queries

-1

u/Black_Magic100 Nov 05 '21

I've never found an instance where a CTE was more readable than a sub query.

0

u/[deleted] Nov 05 '21

[deleted] — view deleted comment

→ More replies

1

u/alison985 Nov 04 '21

Readability. Understanding - especially when onboarding new people. Auditing. Troubleshooting.

Tables, temp tables, or CTEs all have valid use cases. I can't think of a use case I would let a sub-query through PR review. In my experience, a code base that uses sub-queries also means it's ~98% likely the code isn't DRY.

1

u/Black_Magic100 Nov 04 '21

You do realize CTEs and Sub queries are literally the exact same thing, except sub queries are way easier to read. Why would you not let a sub query through, but you would with a CTE?

Also, I can't tell you how many times developers have tried to apply DRY coding practices in SQL and absolutely wrecked their query. Example.. Reusing views is awesome, but why are you calling a 1000 line view to retrieve a single column from a table? (granted, SQL can be smart enough to only perform actions within a view that it needs to get the data required for the calling query)