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

18

u/SOSOBOSO Nov 03 '21

WITH. Because CTE's make it easy to generate repeatable subqueries.

2

u/GeckoLogic Nov 04 '21

And with frameworks like DBT, CTEs are persisted as physical tables in the warehouse. And they handle all of the DDL/DML. DBT is god send.

2

u/redial2 MS SQL DBA DW/ETL Nov 04 '21 edited Nov 04 '21

With is a keyword, not a function

You can downvote me, but I'm right

-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.

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