Many students can write a plain SELECT but lose track when one query appears inside another. The real difficulty is usually one question: does the inner query stand alone, or does it need a value from the current outer row?
Once you can spot that dependency, subquery questions become a sequence of small evaluations instead of one intimidating statement.
The one idea that unlocks every subquery
A subquery is a SELECT nested inside another SQL statement. It can supply one scalar value, a column of values, or a table-shaped result.
The main fork is dependency:
A non-correlated subquery does not refer to the outer query. In the usual placement-test model, evaluate it once and reuse its result.
A correlated subquery refers to a column from the current outer row. Conceptually, evaluate it again for each candidate outer row.
Database optimisers may transform either form internally, but the conceptual model above is what lets you predict the result correctly.
Position also tells you the result's job. A subquery in WHERE supplies a filter. One in FROM becomes a derived table. One in SELECT normally supplies a scalar value for each output row.
The employee and department tables
Every worked answer below uses these five employees:
emp_id | name | dept_id | salary |
|---|---|---|---|
1 | Asha | 10 | 50000 |
2 | Ravi | 10 | 70000 |
3 | Neha | 20 | 60000 |
4 | Vikram | 20 | 45000 |
5 | Priya | 30 | 80000 |
The department lookup is:
dept_id | dept_name |
|---|---|
10 | Sales |
20 | Engineering |
30 | Research |
Keep the data visible as you read. Placement questions often use tiny tables precisely so that you can compute each result by hand.
Non-correlated subquery: runs once
Suppose the question asks for employees earning more than the company average:
SELECT name
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);Nothing in the inner query refers to the outer row. Compute its scalar result:
AVG = (50000 + 70000 + 60000 + 45000 + 80000) / 5
The numerator is 305000, so AVG = 305000 / 5 = 61000.
The outer query is now equivalent to filtering on salary > 61000. Ravi earns 70000 and Priya earns 80000, so they pass. Asha at 50000, Neha at 60000, and Vikram at 45000 fail. The answer is Ravi and Priya.
Read the parentheses first. If they can run without any outer alias, write down their result, then solve the outer query with that value.
Correlated subquery: runs per row
Now change the question: find employees earning more than the average salary of their own department.
SELECT e.name
FROM Employee e
WHERE e.salary > (
SELECT AVG(x.salary)
FROM Employee x
WHERE x.dept_id = e.dept_id
);The reference to e.dept_id creates the correlation. Walk through all five outer employees:
Department 10 average is
(50000 + 70000) / 2 = 120000 / 2 = 60000. Asha's50000fails and Ravi's70000passes.Department 20 average is
(60000 + 45000) / 2 = 105000 / 2 = 52500. Neha's60000passes and Vikram's45000fails.Department 30 average is
80000 / 1 = 80000. Priya earns exactly80000, so she does not satisfy the strict>comparison.
The answer is Ravi and Neha. If the operator were >=, Priya would also pass. That one-character boundary is a favourite predict-the-output twist.
EXISTS and NOT EXISTS
EXISTS asks whether the inner query can produce at least one row. The selected expression is irrelevant, which is why SELECT 1 is conventional.
SELECT d.dept_name
FROM Department d
WHERE EXISTS (
SELECT 1
FROM Employee e
WHERE e.dept_id = d.dept_id
AND e.salary > 65000
);For Sales, Ravi at 70000 supplies a match. Engineering has no salary above 65000, because its maximum is Neha's 60000. Research has Priya at 80000. The answer is Sales and Research.
Think of EXISTS as a true-or-false presence test that can stop as soon as one matching row turns up, which is why it often beats counting rows you do not need.
NOT EXISTS is the same machinery with its answer inverted. To list the departments where nobody earns above 65000:
SELECT d.dept_name
FROM Department d
WHERE NOT EXISTS (
SELECT 1
FROM Employee e
WHERE e.dept_id = d.dept_id
AND e.salary > 65000
);Sales is excluded because of Ravi and Research because of Priya. Engineering has nobody above 65000, so the answer is Engineering alone. Unlike NOT IN, this stays reliable when the compared column holds a NULL, because EXISTS only asks whether a row came back, and that question is always true or false.
The worked examples in SQL Queries and Joins in DBMS help you recognise when the same question can be expressed as a join.
Traps that cost the mark
A scalar operator with several rows
= (SELECT ...) expects one value. If the subquery returns several rows, the database raises an error. Use IN for membership, or use ANY or ALL when the question calls for a quantified comparison.
NOT IN with NULL
If the subquery behind NOT IN contains even one NULL, comparisons against that unknown value prevent the predicate from becoming true in the expected way. The outer query can return no rows. Prefer a properly correlated NOT EXISTS when nulls are possible.
Correlation hidden in an alias
Do not decide by indentation. Scan the inner query for an outer alias. A reference such as e.dept_id is the dependency that makes the query correlated.
Correct result, avoidable repeated work
A correlated form may express the requirement clearly, but it can be expensive on large data. A database may decorrelate it, or you can rewrite it with a join to a grouped derived table. Compare both forms and inspect the plan when performance matters.
For exam-style edge cases, DBMS SQL Query MCQs gives focused practice on SELECT, joins, and subqueries.
How placement tests and interviews frame this
Online assessments commonly give a small table and ask you to predict the result. Interviews may ask you to rewrite a correlated subquery as a join, explain EXISTS versus IN, or diagnose a NOT IN query that unexpectedly returns nothing.
Say your reasoning aloud: identify the inner result shape, check for an outer reference, compute the inner result, then apply the outer predicate. KnowledgeGate's question bank has over 2,000 DBMS questions, including SELECT, joins, and subqueries. The CS Fundamentals for Placements category connects this SQL practice with the wider placement syllabus.
The short version and your next step
Read the inner query first. If it does not need an outer value, compute it once in your hand trace. If it names an outer column, evaluate it for each candidate row. Use EXISTS and NOT EXISTS for presence checks, and treat NOT IN cautiously when nulls are possible.
Next, practise each shape in Coding for Placements: one scalar subquery, one correlated average, one EXISTS, and one null-safe NOT EXISTS. For every query, write the intermediate result before you run it.
