SQL Is Surviving: When One Query Isn't Enough
Learn how to use subqueries and CTEs in SQL through Franklin's journey, covering averages, maximum scores, and handling multiple values with IN.
Stock photo for illustration only, not from the actual event
- Learn how to use subqueries to calculate values before filtering
- Understand the difference between using the = operator and the IN clause
- Explore subqueries inside WHERE and FROM clauses
After learning how to connect tables, link students to classes, and handle relationships in our previous session, Franklin is facing a new challenge where a simple SELECT statement is no longer enough.
Today, we dive into subqueries and CTEs—essential tools when information isn't simply sitting in another table, and we need to calculate something first before answering another question.
For instance, tackling the question "Which students scored above the average?" requires calculating the class average before making any comparisons.
A subquery is simply a query inside another query. The inner query calculates the average (e.g., 78), and the outer query finds students with scores higher than that result.
Stock photo for illustration only, not from the actual event
Subqueries provide a powerful way to modularize your database logic, separating complex calculations from main filtering criteria to keep queries organized and manageable.
Subqueries aren't limited to returning a single value; they can also return multiple rows, such as retrieving relevant class IDs for multiple class names.
- Use the
=operator when expecting exactly one value, such as finding a maximum score. - Use the
INclause when your subquery returns a list of multiple values.
Another common usage places a subquery inside the FROM clause to create a temporary result set that the outer query can treat like a table, assigning it a name using the AS keyword.
Source: Dev.to
Found something wrong in this article? Report an issue with this article
Comments
Leave a Comment