Interview MasterSQL Learning Hub
SQL Subqueries: Queries Within Queries
SQL Subqueries: Queries Within Queries
Learn how to nest queries inside other queries to solve complex database problems
Understanding SQL Subqueries
A subquery (also known as a nested query or inner query) is a query that appears inside another SQL query. Think of subqueries as helpers that provide information to the main query, allowing you to solve complex problems that would be difficult or impossible with a single query.
Mastering subqueries takes your SQL skills to the next level, enabling you to write more efficient and powerful queries. This skill is particularly valuable in technical interviews where interviewers often test candidates on advanced SQL techniques.
Why Use SQL Subqueries?
- ✓Answer complex questions: Find videos with more views than the average, users who have never commented, etc.
- ✓Create dynamic filters: Filter results based on aggregated data or other calculated values
- ✓Perform multi-step operations: Break down complex operations into manageable pieces
- ✓Technical interview essential: Subqueries appear frequently in SQL interview questions
Subqueries vs. Joins: When to Use Each
Both subqueries and joins allow you to work with data from multiple tables, but they serve different purposes. Understanding when to use each approach is a key skill for SQL developers.
| Scenario | Subquery | Join |
|---|---|---|
| Connecting related data from multiple tables | Can work, but may be less readable | Usually preferred for clarity and performance |
| Filtering based on aggregated results | Often clearer and more intuitive | Requires GROUP BY with HAVING, can be more complex |
| Comparing a value to a result set | Natural fit with operators like IN, ANY, ALL | Often requires additional filtering logic |
| Creating derived tables or temporary results | Perfect for value calculations | Can work with derived tables in the FROM clause |
Remember, it's not always an either/or choice. Many complex queries use both joins and subqueries together for maximum power and readability.
Types of SQL Subqueries
SQL subqueries come in several types, each with specific behaviors and use cases. Understanding these differences is crucial for writing effective SQL.
Scalar Subqueries
A scalar subquery returns exactly one column and one row (a single value). These are the simplest form of subqueries and can be used anywhere a single value is expected, such as in SELECT, WHERE, or HAVING clauses.
When to use Scalar Subqueries:
- You need a single calculated value for comparison
- You want to include a calculated value in your results
- You need a dynamic value that can't be hardcoded
TokTuk Example:
-- Find videos with more views than the average
SELECT title, views
FROM Videos
WHERE views > (SELECT AVG(views) FROM Videos)
ORDER BY views DESC;This query first calculates the average number of views across all videos using a scalar subquery, then filters the main query to show only videos that exceed this average. The subquery returns a single value, which is then used in the comparison.
Row Subqueries
A row subquery returns a single row with multiple columns. These subqueries are useful when you need to compare against multiple values simultaneously.
When to use Row Subqueries:
- You need to compare multiple columns at once
- You're looking for exact matches across several criteria
- You need to retrieve multiple values from a related record
TokTuk Example:
-- Find users with the same join date and email domain as user 'alice'
SELECT username, email, join_date
FROM Users
WHERE (join_date, SUBSTRING(email, INSTR(email, '@') + 1)) =
(SELECT join_date, SUBSTRING(email, INSTR(email, '@') + 1)
FROM Users
WHERE username = 'alice')
AND username != 'alice';This query uses a row subquery to find users who joined on the same day and have the same email domain as user 'alice'. The subquery returns a row containing two values (join date and email domain), and the main query compares both values simultaneously.
Table Subqueries
A table subquery returns multiple rows and columns, effectively creating a temporary table that can be used in the FROM clause or with operators like IN, ANY, or ALL.
When to use Table Subqueries:
- You need to query from a filtered or calculated dataset
- You want to create a derived table for further processing
- You need to check if values exist in a filtered result set
TokTuk Example:
-- Find users who have commented on at least one video
SELECT username, email
FROM Users
WHERE user_id IN (
SELECT DISTINCT user_id
FROM Interactions
WHERE interaction_type = 'comment'
);This query uses a table subquery to first identify all user IDs that have made comments. The main query then retrieves user details for those IDs. The subquery potentially returns multiple rows, which are used with the IN operator to filter the main query.
Correlated Subqueries
A correlated subquery is a subquery that depends on values from the outer query. Unlike other subqueries, it executes repeatedly, once for each row of the outer query, making it similar to a loop.
When to use Correlated Subqueries:
- You need to compare each row against a related condition
- You want to filter based on row-specific calculations
- You need to check existence of related records
TokTuk Example:
-- Find users who have more comments than likes
SELECT username, email
FROM Users u
WHERE (
SELECT COUNT(*)
FROM Interactions i
WHERE i.user_id = u.user_id
AND i.interaction_type = 'comment'
) > (
SELECT COUNT(*)
FROM Interactions i
WHERE i.user_id = u.user_id
AND i.interaction_type = 'like'
);This query uses correlated subqueries to count comments and likes for each user individually. For each row in the Users table, the subqueries execute to compare the user's comment count against their like count. The 'u' alias links the subqueries to the outer query.
Ready for hands-on SQL practice?
We have 200+ questions on real companies and real products.
Ask Dawn AI