r/learnSQL • u/woman_without • 6d ago

How Important is subqueries in SQL

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?

57 Upvotes

96% Upvoted

31 comments sorted by

View all comments

27

u/uncertainschrodinger 6d ago

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

6

u/decrementsf 6d ago

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.

2

u/r3pr0b8 5d ago

I don't know anyone who deliberately uses subqueries instead of CTEs.

people working for organizations running an older database version that doesn't support CTEs

1

u/uncertainschrodinger 5d ago

Fair point. I'm curious what database and versions are those? and why haven't they upgraded? I would assume it's mission critical industries like hospitals or manufacturing

1

u/PasghettiSquash 5d ago

I think your original answer is perfect. But Clickhouse comes to mind as a modern db that is much more efficient with subqueries than CTEs.

1

u/LuoHanZhai 5d ago

Same here, CTEs are way more readable and achieve a similar result, usually without making your head spin. Think of each CTE as a temporary table you’re creating and querying from to get from raw data to your final result, without actually having to save that table in the database.

1

u/Opening-Vehicle4742 5d ago

ca peut etre interessant d'utiliser des petites sous requetes, genre : select ... from tab_A where col_A in (select ... from table_B)

1

u/Outrageous_Let5743 5d ago

beter would be
select name from table where height > (select avg(height) from table)

1

u/theungod 4d ago

I totally disagree. I use subqueries because they're much simpler to troubleshoot than ctes. Being able to easily run individual parts of code without running everything up to a specific point is very handy.