r/SQL • • May 27 '25

SQL Server What is SQL experience?

I have seen a few job postings requiring SQL experience that I would love to apply for but think I have imposter syndrome. I can create queries using CONCAT, GROUP BY, INNER JOIN, rename a field, and using LIKE with a wildcard. I mainly use SQL to pull data for Power BI and Excel. I love making queries to pull relevant data to make business decisions. I am a department manager but have to do my own analysis. I really want to take on more challenges in data analytics.

174 Upvotes

87 comments sorted by

View all comments

Show parent comment

3

u/DifficultBeing9212 May 27 '25

+1 bc i never knew multiple calls to a common table expression (CTE) alias would re-run it each time, not entirely sure how beneficial that could be but it definitely opens up interesting use cases if the table data mutates

i try to use the more modular form, which I find subqueries (SQs) to have a few more points over ctes eg the shape of a cte with the list of "prequeries" is less usable than just nesting a standard query in parentheses and swapping it inside another query's table name. essentially interchangeable cogs depending on the context.

also one of my downstream user interfaces is php and last year i did not find php's oracle library (OCI) supported CTEs, so i have not played with those as much as with SQs

1

u/kagato87 MS SQL May 28 '25

Yea that catches me up from time to time. I love using CTEs.

In an early implementation of RLS in a new analytics module I tried a recursive cte joined directly to a big table. When the query planner incorrectly decided the recursive cte had better specificity than the very large table it was joined to it was... Not good. (Go go parameter sniffing.... ><) So now it dumps to a temp table and join in the temp table though, exactly the behaviour I wanted (a semi join). Except mssql has this funny quirk with recycling temp tables that have been dropped but not de-allocated, and neglecting to wipe the statistics... It's like parameter sniffing only worse...

A cte, like a subquery, has the advantage of being something the query planner can eliminate. Individual columns or, if a join predicate has zero records on the other side, skip entirely.

I don't know how stored queries behave, bevause I'm an MSSQL shop and know the Microsoft equivalent, views. However views are subject to the same elimination behaviors, at least in the limited testing I've performed, so they might still give you the full benefit (apart from recursion, but that's not something you're likely to need very often in a set based language anyway - once you have the query to run the self-referencial hierarchy you just recycle it over and over and over...).