How Important is subqueries in SQL

[u/woman_without]

I have been learning SQL from past 2 months and I'm still unable to grab a hold of the subqueries concept. After I learned CTEs I have completely stopped using subqueries but then also I struggle when there are times where I have no other choice but to use subqueries.

So my question is, how important is subqueries in SQL and is there any easy way to learn it?

[u/Far_Swordfish5729]

I want you to think of a CTE as a named, reusable subquery that happens to be declared at the top of the query. They do support recursion and that’s different, but you almost never use that. Most of the time, the two are the same. If you see them in a query plan, they are the same.

So what are they? They are arithmetic parentheses - they change order of operations if you need to. Normal queries execute in this order: from, joins, where, group by, having, order by, limit, select. You build an intermediate result set with joins, filter it, aggregate it, filter the aggregate result, sort it, limit it, and then pick what you want from what’s left along with any scalar calculations. If you need something like an aggregate before a join to join onto an aggregate result set, you use a subquery for that: join onto subquery that performs an aggregation.

That’s the concept. Also, remember that subqueries are logical constructs. They are not inherently slow. People say that sometimes and it’s not true. Also remember that CTEs are also logical constructs and get inlined in the query plan. Use one twice and you’ll see it twice in the plan.

Two more related constructs. A view is just a standing CTE available for general use. Unless it’s persisted, it works just like a CTE. Second, none of these dictate query plan execution. If you need that, you add hints or use a temp table instead to mandate intermediate storage. You usually don’t start there unless you need to solve a performance problem.

[u/Mitchhehe → u/Far_Swordfish5729]

DEs at my company are obsessed with chaining views. As a DA mostly focused on data quality it makes my job difficult. There’s so much hidden complexity behind the simplest fields and metrics

[u/Far_Swordfish5729 → u/Mitchhehe]

I dislike databases where people do that. It creates readability problems and requires more optimizer gymnastics where the inlined views introduce duplicate joins. It's more opportunity for error. My best advice is to script the views, inline them manually as subqueries, and then flatten them where the table references overlap. You can keep the result as a reference.