Types of Subqueries
Subqueries can be categorized based on where they appear in an SQL statement and their purpose. They are categorized as,
- Nested Scalar Subquery
- Single-Row Subquery
- Multiple-Row Subquery
- Single-Column Subquery
- Correlated Subquery
Example Data
The given table will be used for the demonstration of subtypes of nested queries.
1. Nested Scalar Subquery
A Nested Scalar Subquery returns a single value and is often used in a comparison. In this example, we retrieve the student_name where the student_id is the maximum.
Query
SELECT student_name FROM students WHERE student_id = (SELECT MAX(student_id) FROM students);
SELECT student_name
FROM Students
WHERE student_id = (SELECT MAX(student_id) FROM Students);Result
2. Single-Row Subquery
A Single-Row Subquery is used to fetch a specific row from the database table, in this case, retrieving the student_name and age where the student_id is the maximum in the students table.
Query
SELECT student_name, age
FROM Students
WHERE student_id = (SELECT MAX(student_id) FROM Students);Result
3. Multiple-Row Subquery
A Multiple-Row Subquery filters results based on a condition involving multiple rows. Here, we retrieve student_name for those in departments where at least one student is older than 21.
Query
SELECT student_name
FROM Students
WHERE department IN (SELECT DISTINCT department FROM Students WHERE age > 21);Result
4. Single-Column Subquery
A Single-Column Subquery is used to extract a single column's value from the database. In this example, we fetch the student_name where the student_id is less than the maximum.
Query
SELECT student_name
FROM Students
WHERE student_id < (SELECT MAX(student_id) FROM Students);Result
5. Correlated Subquery
A Correlated Subquery references columns from the outer query, creating a connection. Here, we fetch student_name for those whose age is greater than the average age in their respective departments.
Query
SELECT student_name
FROM Students s
WHERE age > (SELECT AVG(age) FROM Students WHERE department = s.department);Result
🤖 Don't fully get this? Learn it with Claude
Stuck on Types of Subqueries? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.
Build the mental picture, not memorization.
I just read a lesson on **Types of Subqueries** (Databases) and want to truly understand it. Explain Types of Subqueries from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Socratic — adapts to where you're stuck.
Teach me **Types of Subqueries** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Active recall exposes what you missed.
Quiz me on **Types of Subqueries** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Intuition + hook + flashcards for long-term memory.
Help me remember **Types of Subqueries** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.