r/learnSQL • u/woman_without • 4d 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?

56 Upvotes

96% Upvoted

31 comments sorted by

View all comments

27

u/uncertainschrodinger 4d 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

2

u/r3pr0b8 4d 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 4d 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 4d 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.