DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Nested Queries Subqueries
Last Updated : 21 May, 2026
A subquery also called a nested query or inner query is a SELECT statement embedded inside another SQL statement. The outer query uses the result of the inner query.
Unit 2275 words12 headingsTables includedExamples included
What is a Subquery?
A subquery (also called a nested query or inner query) is a SELECT statement embedded inside another SQL statement. The outer query uses the result of the inner query.
SELECT ...
FROM ...
WHERE column operator (SELECT ... FROM ... WHERE ...);
└─────── subquery ───────────┘
Types of Subqueries
By Location
| Location | Example |
|---|---|
| WHERE clause | Most common |
| FROM clause | Derived table / inline view |
| SELECT clause | Scalar subquery |
| HAVING clause | Aggregate filtering |
By Result
| Type | Returns | Operators Used |
|---|---|---|
| Scalar | Single value (1 row, 1 col) | =, <, >, <=, >=, != |
| Column | Single column, multiple rows | IN, NOT IN, ANY, ALL |
| Table (Derived) | Multiple rows and columns | Used in FROM clause |
| Correlated | Depends on outer query | EXISTS, NOT EXISTS |
1. Scalar Subquery
Returns exactly one value — used with comparison operators.
sql exampleWoHoTech
-- Find employees earning more than the average salary
SELECT name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);
-- Find the employee with the highest salary
SELECT name, salary
FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee);
-- In SELECT clause (returns one value per row)
SELECT name,
salary,
(SELECT AVG(salary) FROM Employee) AS company_avg,
salary - (SELECT AVG(salary) FROM Employee) AS diff_from_avg
FROM Employee;2. IN / NOT IN Subquery
Subquery returns a list of values; outer query checks membership.
sql exampleWoHoTech
-- Employees who work in the CS or Math departments
SELECT name FROM Employee
WHERE dept_id IN (
SELECT dept_id FROM Department
WHERE dept_name IN ('Computer Science', 'Mathematics')
);
-- Employees who do NOT work on any project
SELECT name FROM Employee
WHERE emp_id NOT IN (
SELECT DISTINCT emp_id FROM Works_On
);Caution:NOT INbehaves unexpectedly with NULLs. UseNOT EXISTSfor safety.
3. ANY / ALL Subquery
ANY— True if condition holds for at least one value in the listALL— True if condition holds for all values in the list
sql exampleWoHoTech
-- Employees earning more than ANY employee in dept 2
SELECT name, salary FROM Employee
WHERE salary > ANY (
SELECT salary FROM Employee WHERE dept_id = 2
);
-- Equivalent to: salary > MIN(salary in dept 2)
-- Employees earning more than ALL employees in dept 2
SELECT name, salary FROM Employee
WHERE salary > ALL (
SELECT salary FROM Employee WHERE dept_id = 2
);
-- Equivalent to: salary > MAX(salary in dept 2)4. EXISTS / NOT EXISTS (Correlated Subquery)
EXISTS returns TRUE if the subquery returns at least one row.
A correlated subquery refers to columns from the outer query — it is re-executed for each row of the outer query.
sql exampleWoHoTech
-- Find departments that have at least one employee
SELECT dept_name FROM Department d
WHERE EXISTS (
SELECT 1 FROM Employee e
WHERE e.dept_id = d.dept_id -- reference to outer query
);
-- Find employees who are not enrolled in any project
SELECT name FROM Employee e
WHERE NOT EXISTS (
SELECT 1 FROM Works_On w
WHERE w.emp_id = e.emp_id
);
-- Correlated subquery flow:
-- For each row in Department (outer), run the inner query
-- If inner returns any row → EXISTS = TRUE5. Derived Table (Subquery in FROM)
A subquery in the FROM clause acts as a temporary (inline) table.
sql exampleWoHoTech
-- Average salary by department, then filter
SELECT dept_id, avg_sal
FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept_id
) AS dept_averages
WHERE avg_sal > 70000;
-- Top earner per department
SELECT e.name, e.salary, e.dept_id
FROM Employee e
JOIN (
SELECT dept_id, MAX(salary) AS max_sal
FROM Employee
GROUP BY dept_id
) AS top ON e.dept_id = top.dept_id AND e.salary = top.max_sal;6. Subquery in HAVING
sql exampleWoHoTech
-- Departments with average salary above the company-wide average
SELECT dept_id, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM Employee);Correlated vs. Non-Correlated Subqueries
Non-correlated
Inner query runs once, then its result is used by the outer query.
Correlated
Inner query depends on the current outer row and may run repeatedly.
WITH Clause (Common Table Expression / CTE)
A cleaner way to write complex subqueries — defines a named temporary result set.
sql exampleWoHoTech
-- Using CTE instead of nested derived table
WITH DeptAvg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM Employee
GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM Employee e
JOIN DeptAvg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;CTEs improve readability and can be referenced multiple times in the same query.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Nested Queries Subqueries.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, nested, queries, nested queries subqueries
Related DBMS Topics
Continue learning this concept
Unit 2Unit 2 — Relational Model & SQLThis unit covers the formal foundations of relational databases and the Structured Query Language SQL. Starting from the mathematical basis of relational algebra and calcUnit 2Constraints in SQLConstraints are rules applied to table columns that restrict the type of data that can be inserted or updated. They enforce data integrity at the database level — indepenUnit 2CursorsA cursor is a database object that allows row-by-row processing of query results inside a stored procedure or PL/SQL block. While SQL naturally operates on sets of rows, Unit 2DCL and TCL CommandsDCL commands control access and permissions to database objects. They manage security by defining who can do what.Unit 2DDL CommandsData Definition Language DDL commands are used to define, modify, and remove database objects such as tables, indexes, views, and schemas. DDL commands affect the structuUnit 2DML CommandsData Manipulation Language DML commands are used to manipulate the data stored in database tables. DML operates on the instance rows/data, not the schema.