Single-Valued Subqueries

A single-valued subquery is a query that 1) produces a result with a single column and a single row and 2) is ’nested’ in the WHERE clause of another query. Subqueries must be enclosed in parentheses.

A single-valued queries:

SELECT AVG(days_to_cutoff)
FROM accounts;

Sing-valued sub-query:

SELECT * 
FROM accounts
WHERE days_to_cutoff > (SELECT AVG(days_to_cutoff)
FROM accounts)
ORDER BY days_to_cutoff;

Key Points:

  • Single-valued queries can be used in comparisons
  • Subqueries are enclosed in parentheses

Multi-Valued Subqueries

A multi-valued subquery is a query that 1) returns a single-column result with zero, one, or more rows and 2) is ’nested’ in the WHERE clause of another query. Multi-valued subqueries always follow the IN operator.

SELECT AVG(days_to_cutoff)
FROM accounts 
WHERE angaza_id IN (SELECT angaza_id
FROM accounts
WHERE area="Ilorin");

Key Point: Multi-valued subqueries follow the IN operator.