Subqueries and Exists

1. Subqueries and Exists

2. Subqueries • A subquery is a SELECT statement that is used to aid data selection by being nested within another SQL query • Are often used with in IN predicate • A subquery is a binary logical check, True or False

3. Differences Between Subquery and Join • Subqueries are a primitive version of a join • Just like a join, a subquery matches the rows of one table to the rows of another table • Unlike a join, a subquery cannot return data from the inner table to the answer set

4. Subquery Example • Find all authors who have written a book Select au_lname, au_fname From authors Where au_id in (select distinct au_id from titleauthor) • Go to Query Analyzer and run this query

5. What Happens with a Subquery • In this case, the innermost query is resolved, building a list of values • The outer query is evaluated against that list like a normal IN predicate

6. Scalar Subqueries • Find the book title having the lowest year to date sales • YTD Sales is in the TITLES table • We can get the lowest YTD Sales with a min function, but in doing that we lose the title (because we have to do some kind of grouping) • however

7. Scalar Subquery Example • We can solve this with a subquery Select title From titles Where ytd_sales = ( select min(ytd_sales) from titles) • Go to Query Analyzer and try this

8. Further Examples • We don’t have to use equality checks • See if you can modify your query to find all titles that have greater than average ytd_sales

9. Greater than Average Sales Select title From titles Where ytd_sales > ( select avg(ytd_sales) from titles)

10. Correlated Subquery • A correlated subquery is a further refinement of the subquery • It correlates or coordinates the two queries • The scope of the inner query becomes dependent on the row being looked at in the outer query

11. Correlated Subquery Problem • Find all titles where the ytd sales for that title is greater than the average ytd sales for the publisher of the title • Notice in this problem we have to find the average ytd_sales for the publisher of a title. • That average number is dependent on the title we are testing • We need to coordinate the subquery with the outer query

12. Correlated Subquery Example select title, ytd_sales from titles t1 where ytd_sales > ( Select avg(ytd_sales) from titles t2 where t1.pub_id = t2.pub_id) • Go to Query Analyzer and run this query Note the coordination Note the coordination

13. Exists Predicate

14. EXISTS • Specifies a subquery to test for the existence of rows. • Returns TRUEif a subquery contains any rows.

15. Exists Problem • Find all Publishers that publish business books • A business book is determined by a type = ‘business’ in the titles table • Find the Publishers that have books with this type

16. Exists Example SELECT DISTINCT pub_name FROM publishers p WHERE EXISTS (SELECT * FROM titles t WHERE t.pub_id = p.pub_id AND type = 'business') • Go to Query Analyzer and run this query

17. What it does • Takes the value from Publishers for pub_id and checks Titles to see if that value is present for any row in the table. If there is and all other where conditions evaluate as true, then the subquery is true and the Exists requirement is met.

18. Further Notes • Is a form or Correlated Subquery • The values returned are not important, the fact that a row (any row) is returned is what is important • The ‘NOT’ phrase can be used to check for a NOT EXISTS condition

19. Exists Vs. Join • In some cases there may have a choice of using either an exist or do a join • Either technique may present a proper solution • Consider…

20. Join Example Select distinct pub_name From publishers p inner join titles t on (t.pub_id = p.pub_id) Where type = 'business‘ • Which is better, Join or Exists?

21. Exists Example Select distinct pub_name From publishers p Where exists ( Select 1 from titles t where p.pub_id = t.pub_id and type = ‘business’)