As you dive deeper into SQL queries, you'll find that sometimes a straightforward SELECT, WHERE, or JOIN just won't be efficient.
A good example is you're managing an e-commerce database, and your boss comes to you with a question:
"Can you tell me which customers have spent more than the average customer?"
Sure, you could work with the numbers manually and produce another query, but SQL has a more efficient way to tackle this. That’s where subqueries and Common Table Expressions come in.
SubQueries
This is a SQL query placed inside another SQL query. The inner query runs first, and its result is then used by the outer query.
An example is this orders table below
If we want to find orders that are above the average order value, we can use a subquery:
The inner query calculates the average order amount while the outer query returns only orders where the amount is greater than the average.
Subqueries are really useful when the result of one query is needed by another query
Common Table Expressions
This is a temporary named result set that can be referenced within a larger SQL query. An example is:
The CTE first calculates how much each customer has spent. We then query the temporary result shown and find customers who have spent more than 5,000.
Conclusion
The main two differences between the two that I would say are first Subqueries are written inside another query while CTEs are defined using WITH
Another difference I'd say is that Subqueries are good for smaller operations while CTEs are good for breaking down complex queries. The most important thing is not simply knowing the syntax but knowing when to use each technique.
Top comments (0)