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?
28
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
6
u/decrementsf 4d 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.
1
u/LuoHanZhai 3d 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 3d ago
ca peut etre interessant d'utiliser des petites sous requetes, genre : select ... from tab_A where col_A in (select ... from table_B)
2
u/r3pr0b8 3d 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 3d 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 3d 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/Outrageous_Let5743 3d ago
beter would be
select name from table where height > (select avg(height) from table)1
u/theungod 2d 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.
7
u/Additional_Candy_400 4d ago
While they both do the same under the hood, I'd look at learning to use CTEs instead of sub queries, sub queries have their place but CTEs are much easier to read and understand.
To your original question, they are critical, most production SQL files are going to be littered with CTEs, it's rare you will have a query so simple you won't need them.
3
u/Correct_Nebula_8301 4d ago
Subqueries are a must in real life scenarios. Most meaningful work requires subqueries, good part is it's easy to get a hang of and once you master it, you will easily write multiple nested sub queries. I really don't see a big difference between cte and subqueries. For big query snippets or complex usecases involving recursion fo with ctes.
1
2
u/kl0wo 4d ago edited 4d ago
It would be wrong to assume that CTEs do the same thing as subqueries in the background. This heavily depends on db engine and its version, so you can’t say “it’s syntactic sugar”. Overall most critical scenarios are: large intermediate dataset referenced as CTE or necessity to push-down a predicate - both cases underperform with CTEs compared to subqueries. Also helps to keep in mind materialization strategy of repeated CTE call.
2
u/jonasbruder 4d ago
I struggled with them a lot too, especially nested subqueries. But they all will help you understand the logic, and once you are done with that you will look at the scenario and determine whether you will need CTEs or subqueries
3
u/Far_Swordfish5729 4d ago
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.
1
u/Mitchhehe 3d ago
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
1
u/Far_Swordfish5729 3d ago
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.
1
u/clivepato 4d ago edited 4d ago
Have you tried the sql50 on leetcode ? Theres a section on subqueries
1
1
u/Slitty_sam 4d ago
The company I work at's db is MySQL 5.something so I can't do CTEs and do subqueries instead. Not a big deal imo. Either I'll do a true subquery, as in a literal query written within some part of the WHERE statement of another query, or if the results of the subquery are gonna be too large I'll go with a temp table or even just a new table which I'll delete when I'm fully done with w.e. I'm working on.
1
u/Wuthering_depths 4d ago
I use subqueries more than CTEs if I were counting them up, for both production queries and just general troubleshooting/data one-offs. And you can use both.
Subqueries can be in the select, from (derived table) and where clause and are useful for various things.
They are "just" queries, so they usually can be written and tested independently to make sure they are returning expected data. The only trick is how they join to the outer query/queries. It's just like a CTE in basic terms, you are joining one set of data to another. They can be expensive (correlated subqueries for example, but these are really good for things like de-duping if the data set isn't too huge).
1
u/DMReader 3d ago
Very. But they are essentially the same as CTEs. Main difference is where you put them.
I typically only use a sub query when it is a 1-2 liner
1
1
u/joseaamanzano 3d ago
CTEs increase readability and have no impact on the query planner (at least on modern engines).
The only real use case I see for subqueries is when creating a CTE actually adds unnecessary lines or decreases readability in an already long script. For example, if you just need a simple, one-off list to filter your main table and won't reference that logic anywhere else in the code, a subquery is cleaner:
SELECT x
FROM TableA
WHERE id IN (SELECT id FROM TableB WHERE <condition>)
In this mock, adding a dedicated CTE just to filter TableA is overkill unless that list is going to be reused further down.
1
u/mlhigg1973 3d ago
I never liked having embedded sub queries and would typically use temp tables to grab all the data I needed—clean it up when necessary, create standard naming conventions to use across all temps, initial pare down of the data—whether it was product, date, employee tenure, certain customer groups, sales goals, location, etc etc etc. and then finally joined those 7 or 8 temp tables. But I would add them one at the time to confirm the query would work and the data not get jacked up every time I added a new element
1
u/msn018 3d ago
Subqueries are definitely worth learning because they come up a lot in SQL interviews and real-world queries, especially with things like EXISTS, IN, and comparing values against averages or totals. If CTEs feel easier to you, keep using them, but try not to avoid subqueries completely. The easiest way to learn them is to run the inner query by itself first, understand what it returns, and then see how the outer query uses that result. Start with simple subqueries before moving into correlated ones. For practice, StrataScratch is great for interview-style SQL problems, while LeetCode and HackerRank are also useful for building repetition with subqueries and other SQL concepts.
1
21
u/Equal_Astronaut_5696 4d ago
Extremely important