SQL SubQuery

SQL SubQuery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

A subquery may occur in :

– A SELECT clause

– A FROM clause

– A WHERE clause

A subquery is also called an inner query or inner select, while the statement containing a subquery is called an outer query or outer select. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query.

Types of SubQuery

There are three broad divisions of subquery:

Single-Row SubQuery

A single row subquery returns zero or one row to the outer SQL statement. Single-row subqueries are used with a comparison operator in a WHERE, or HAVING clause.

Syntax of single-row SubQuery

SELECT * FROM table
WHERE columnName > (SELECT columnName
FROM table2)

Example of single-row SubQuery

If we want to display records of all those pets who are the oldest among all pets, we can write following query:

SELECT * FROM Pets
WHERE Age=(SELECT MAX(Age) FROM Pets);

Output:

Multiple-Row SubQuery

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

Syntax of Multiple-row SubQuery

SELECT * FROM table
WHERE columnName IN (SELECT columnName
FROM table2);

Example of Multiple-row SubQuery

If we want to display records of all those owners who own a pet, we can write following query:

SELECTOwnerID,Name,Surname FROM Owners
WHERE OwnerID IN (SELECT OwnerID FROM Pets);

Output:

Correlated SubQuery

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. 

In other words, you can use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement. In this type of queries, a table alias (also called a correlation name) must be used to specify which table reference is to be used. EXISTS operator can also be used in correlated subqueries. It is used to test for the existence of any record in a subquery.

Syntax of Correlated SubQuery

SELECT * FROM table
WHERE column EXISTS (SELECT columnName
FROM table2);

Example of Correlated SubQuery

If we want to display records of all pets owned my a particular owner, by his name, we can write following query:

SELECT * FROM Pets as p
WHERE EXISTS (SELECT OwnerID FROM Owners as o WHERE p.OwnerID=o.OwnerID AND o.Name='Jessica');

Output: