SQL Nested Queries

1. SQL Nested Queries Jeffrey P. Landry University of South Alabama University of South Alabama School of CIS

2. Nested Query-Defined • A nested query is a query within a query • A complete SELECT statement embedded within another SELECT statement • Nested queries are also called subqueries University of South Alabama School of CIS

3. Query Results • The results of various queries can be thought of as: • a table (multiple rows and columns) • a list of values (a single column) • or a single value (one row, one column, i.e. a cell) • Subqueries may be placed wherever you can place a table, list of values, or a single value University of South Alabama School of CIS

4. Subqueries that Return a Single Value • Queries that return a single value (cell) can be used wherever a literal value is allowed • Example: List all book titles who are priced above the average price SELECT Avg(price) FROM titles SELECT title, price FROM titles WHERE price > (SELECT Avg(price) FROM titles)  University of South Alabama School of CIS

5. Subqueries that Return a List of Values • Queries that return a list of values (from one column) can be used wherever a value list is required (eg. the IN operator) • Example: List all National League players SELECT TeamId FROM Team WHERE League = ‘NL’  { Same as (2, 3, 4, 8, 9, …) } SELECT Last, First FROM Player WHERE TeamId IN (SELECT TeamId FROM Team WHERE League = ‘NL’) University of South Alabama School of CIS

6. Subqueries that Return a Table • Queries that return a table (multiple rows and columns) can be used wherever a table is required • For example, in the FROM statement • Use aliasing (AS <tablename> • Example: Highest-priced titles by publisher University of South Alabama School of CIS

7. Example of Nested Query that Return a Table SELECT pub_id, Max(price) -- set of top-prices FROM titles -- by publisher GROUP BY pub_id -- highest-priced titles by publisher SELECT title, titles.pub_id, price FROM titles, (SELECT pub_Id, Max(price) AS TopPrice FROM titles GROUP BY pub_id) As TopPubPrice WHERE (titles.pub_id = TopPubPrice.pub_id) AND (titles.price = TopPubPrice.TopPrice) University of South Alabama School of CIS

8. Nested Queries and EXISTS • EXISTS asks “does at least one row exist?” • Accepts a SELECT query as its lone parameter • Interprets SELECT in boolean terms • True means there is at least one row that satisfies the query • False means empty set is returned • The nested SELECT query must refer to the outer query (correlated query), and is executed for each row in the outer query University of South Alabama School of CIS

9. EXISTS example Problem: List all the departments that have no employees Solution: SELECT DeptName FROM Department WHERE NOT EXISTS (SELECT LastName FROM Employee WHERE Employee.DeptId=Department.DeptId); University of South Alabama School of CIS

10. EXISTS example Problem: List all publishers that have no titles Solution: SELECT pub_id FROM publishers WHERE NOT EXISTS (SELECT pub_id FROM titles WHERE titles.pub_id=publishers.pub_id); University of South Alabama School of CIS