Introduction to SQL


Subqueries

This document explains using subqueries as an alternate method to the join clause and providing additional functionality to the join clause.

Contents

  • Subqueries - Introductory Example
  • Subqueries Overview
  • Subqueries Restrictions
  • Multiple Levels of Nesting
  • Subqueries returning Multiple Rows
  • Subqueries with comparison operators

    • Subqueries - Introductory Example

  • Example: What book titles are published by "New Age Books?" 
        select title
      from titles, publishers
      where pub_id =
         (select pub_id 
          from publishers
          where pub_name = "New Age Books")

    • Subqueries Overview

  • A subquery is a select statement, used as an expression in part of another select, update, insert, or delete statement
  • Subquery is resolved and the results substituted into the outer query's where clause

    • Subqueries Restrictions

  • If the where clause of the outer query includes a column name, the column must be join-compatible with the column named in the subquery select_list
  • Cannot include order by or compute clause or the into keyword
  • Cannot use distinct with subqueries that include a group by

    • Multiple Levels of Nesting

  • A subquery may contain one or more subqueries
  • Example: What is the title of the book written by Blochet-Halls. 
        select title
      from titles
      where title_id =
         (select title_id 
          from titleauthor
          where aud_id =
             (select aud_id 
              from authors
              where au_lname = "Blochet-Halls"))

    • Subqueries returning Multiple Rows

  • If the subquery might return more than one value, you must use in in the outer query's where clause instead of =
  • Example - List books sold by stores located in California 
        select distinct stor_id, title_id
           from sales_detail
           where stor_id in
           (select stor_id from stores 
                           where state = "CA")

    • Subqueries with comparison operators

  • Subqueries can perform some tasks join clauses cannot.
  • A where clause in a select statement cannot include an aggregate
  • A subquery can include an aggregate
  • Example (greater than an aggregate expression) 
        select title_id, price
           from titles 
           where price >
             (select avg(price)
                     from titles)
    • [HOME] [TOP] [PREVIOUS]
    Darrick Jones <DARRICK_JONES@ccmail.sel.sony.com>
    Created: July 17, 1998