Subqueries

A subquery is a query (SELECT statement) inside a query, normally expressed inside parentheses; it is also called a nested query. The first query in the SQL statement is the outer query, the one inside is the inner query. For simple non-correlated queries, the inner query is executed first, and the output of inner query is input to outer query. The subquery is executed only once for the whole statement. The correlated subquery is dependent on the outer query. It is executed once per every row considered in the outer query.

There are 3 basic types of subqueries in SQL:

• Predicate subqueries: extended logical constructs in the WHERE clause (and in HAVING clause when grouping data, that we will see later).
• Scalar subqueries: standalone queries that return a single value; they can be used anywhere a scalar value is used.
• Table subqueries: queries that can be nested in the FROM clause. Subqueries are enclosed in parentheses.

<aside> 🛎️ Ensure that you re-run the SQL Script below.

InvoicesDBINIT.sql

</aside>

Use of a subquery in the WHERE clause

• Try the following query to list customers who have made at least one purchase:

SELECT customerCode, lastName, firstName FROM Customer
WHERE customerCode IN (SELECT customerCode FROM Invoice);

• List the vendors who have not supplied any product yet
• List the customers who have the highest balance among all other customers.

Multi-row subquery operators: ANY and ALL

• The ALL keyword specifies that the search condition is TRUE if the comparison is TRUE for every value that the subquery returns. Try the following query to list the most expensive product(s) in inventory:

SELECT productCode, description, price FROM Product
WHERE price >= ALL (SELECT price FROM Product);

• The ANY keyword denotes that the search condition is TRUE if the comparison is TRUE for at least one of the values that is returned. Try the following query:

SELECT productCode, description, price, vendorCode FROM Product WHERE price < ANY (SELECT price FROM Product WHERE vendorCode = 21344)AND vendorCode <> 21344;

What does it output?