Where to use subqueries

1. In a WHERE clause as a search condition
2. In a HAVING clause as a search condition
3. In the FROM clause as a table specification
4. In the SELECT clause as a column specification

      SELECT productName, listPrice 
      FROM products 
      WHERE listPrice > (
        SELECT AVG(listPrice)
        FROM products
        );
      ORDER BY listPrice DESC;
      

The result set

      The value returned by the subquery 841.895 
      

The result set

      SELECT productName, listPrice 
      FROM products 
      WHERE categoryID = (
        SELECT categoryID 
        FROM categories 
        WHERE categoryName = 'Basses'
        );
      

The result set

Description

• A subquery is a SELECT statement that’s coded within another SQL statement.
• A subquery can return a single value, a column that contains multiple values, or multiple columns that contain multiple values.
• A subquery that returns a single value can be coded, or introduced, anywhere an expression is allowed. A subquery that returns a single column can be introduced in place of a list of values, such as the values for an IN clause. And a subquery that returns one or more columns can be introduced in place of a table in the FROM clause.