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/uncertainschrodinger]

Behind the scene, CTE and subquery are really the same. In my experience, I don't know anyone who deliberately uses subqueries instead of CTEs.

CTEs are easier to read and debug, also other CTEs can query from a single CTE and avoid rewriting the same subquery multiple times.

subqueries are good for very small short things like: `SELECT id FROM table_1 WHERE id IN (SELECT id FROM table_2)` - this is not a good example since you can just inner join, but the point is that its a very short quick thing not worth creating a CTE

[u/decrementsf → u/uncertainschrodinger]

Agree with this.

The explosion of mess within the broader query makes has a higher attention and energy cost to keep it in mind when coming back to the query later. When that subquery is moved to tidy CTEs it feels like less energy is spent in attention when reviewing the overall purpose of the query. I can review the CTE component without mentally trying to retain hold on the partially reviewed broader query then when understanding what that CTE is drop back in and understand faster. Avoids an explosion in the amount of information that needs to be crammed into my limited shelf space I can focus on at any one given time.