A nested query or subquery is a query within a query. Subqueries are usually added in the WHERE clause of the SQL statement when you know the conditions for a field’s value but do not know the exact value in the database. Conditions can be created using comparison operators such as =, IN, EXIST or UNIQUE.

Comparing single values from subqueries

When nested queries return a single value, the returned value can be compared using  =, <, >, etc. Example: finding student(s) who got the best grade registered.

SELECT StudentID, FinalGrade
FROM Registrations
WHERE FinalGrade = (SELECT MAX (FinalGrade) FROM Registrations)

The nested query returns the maximum grade recorded, which is used in the parent query to find the corresponding student.

IN operator

Compares a field to each value in a list returned by a nested query. Example: names of students who have registered in BAMS 580D.

SELECT StudentName
FROM Students
WHERE StudentID IN (SELECT StudentID FROM Registrations WHERE CourseID= ‘BAMS 580D’)

EXISTS operator

Returns TRUE if the result set of the subquery is not empty. Example: names of students who have registered in BAMS 580:

SELECT S.StudentName
FROM Students S
WHERE EXISTS (SELECT * FROM Registrations R
WHERE R.CourseID= ‘BAMS 580’ AND S.StudentID = R.StudentID)

The subquery is recomputed for each Student tuple of the first query so the fields from the parent query can be used in the nested query.

Other subquery operators

Other operators to compare results from nested queries are:

UNIQUE	Checks for duplicate tuples in a subquery and returns true if there are no duplicates.
ALL	Compares a value with every value in a list or returned by a query. Must be preceded by =, >, <, etc. Evaluates to TRUE if the query returns no rows.
ANY	Compares a value to each value in a list or returned by a query. Must be preceded by =, >, <, etc. Evaluates to FALSE if the query returns no rows.

Subqueries in FROM clause

Used in the FROM clause, nested queries are an alternate way of pre-processing data from tables, for instance to filter students from 2014:

SELECT Students2014.*
FROM (SELECT StudentID FROM Students WHERE Year = 2014) as Students2014

WITH clause

Another way of creating nested queries is using the WITH clause (not available in MS Access), which creates a temporary table that can be queried.

See SQL subquery to read more about subqueries. See Oracle SQL Operators to read more about operators.