1 / 55

Chapter 8 - Subqueries

Chapter 8 - Subqueries. A Subquery nests a SELECT statement in the WHERE clause of another SELECT, INSERT, UPDATE, or DELETE statement A Subquery’s Outer SELECT operates on rows returned from the Inner SELECT The Inner and Outer SELECT statements may refer to the same table.

jeannettem
Download Presentation

Chapter 8 - Subqueries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 - Subqueries • A Subquery nests a SELECT statement in the WHERE clause of another SELECT, INSERT, UPDATE, or DELETE statement • A Subquery’s Outer SELECT operates on rows returned from the Inner SELECT • The Inner and Outer SELECT statements may refer to the same table

  2. From the Inside Out • Most Subqueries are evaluated from the inner query to the outer query • A correlated Subquery, discussed later, works in reverse

  3. Simplified Subquery Syntax • SELECT select_listFROM table_listWHERE expression { [NOT] IN | operator | [NOT] EXISTS } (SELECT subquery_select_list FROM table_list WHERE search_conditions);

  4. Subquery Options • Produces an ‘IN’ list of values for evaluation by the outer query • Returns a single value for comparison • Returns a TRUE/FALSE value for an existence test

  5. Why Use a Subquery? • Subqueries can be difficult to read • Many can be replaced with an equivalent join • A subquery is sometimes the only alternative

  6. Example - without a Subquery • Find all the books that have the same price as Straight Talk About Computers • SELECT priceFROM titlesWHERE title = 'Straight Talk About Computers'; • SELECT title, priceFROM titlesWHERE price = $19.99;

  7. Example - Using a Subquery • SELECT title, priceFROM titlesWHERE price = (SELECT price FROM titles WHERE title = 'Straight Talk About Computers’);

  8. Joins vs. Subqueries • Join • Advantages: Can return results from all tables • Disadvantages: May need two statements to get desired results • Subquery • Advantages: Can compare aggregates to other values. Can get information in a single statement • Disadvantages: Can only return results from the Outer Select statement

  9. Two Ways • SELECT pub_name, au_lnameFROM publishers p, authors aWHERE p.city = a.city; • SELECT pub_nameFROM publishersWHERE city IN (SELECT city FROM authors);

  10. The IN operator • Use the ‘IN’ operator when the results of the inner query will have zero, one, or more values • Once the inner query returns results, the outer query will make use of them

  11. Example • SELECT pub_nameFROM publishersWHERE pub_id IN (SELECT DISTINCT pub_id FROM titles WHERE type = 'business');

  12. Conceptually... • Inner Query- • SELECT DISTINCT pub_idFROM titlesWHERE type = 'business';(2 rows returned) • Outer Query Evaluates the 2 rows - • SELECT pub_nameFROM publishersWHERE pub_id IN ('1389', '0736');

  13. Done as a Join • SELECT DISTINCT pub_name, titleFROM publishers p, titles tWHERE p.pub_id = t.pub_idAND type = 'business'; • With Joins you can list columns from both tables (see difference when title is left off the select list)

  14. Another Example as a Join • Find the names of all authors listed 2nd on a book who live in California and receive less than 30 percent of the royalties • SELECT au_lname, au_fnameFROM authors a, titleauthors taWHERE state = 'CA'AND a.au_id = ta.au_idAND royaltyshare < .30AND au_ord = 2;

  15. Example as a Subquery • SELECT au_lname, au_fnameFROM authorsWHERE state = 'CA'AND au_id IN (SELECT au_id FROM titleauthors WHERE royaltyshare < .30 AND au_ord = 2);

  16. Exercise - Use Subqueries • List the author's names for all books with more than one author

  17. Answer • SELECT au_lname, au_fnameFROM authorsWHERE au_id IN (SELECT au_id FROM titleauthors WHERE title_id IN (SELECT title_id FROM titleauthors WHERE au_ord > 1));

  18. Using NOT IN • SELECT pub_nameFROM publishersWHERE pub_id NOT IN (SELECT DISTINCT pub_id FROM titles WHERE type = 'business');

  19. NOT IN Does Not Equal < > • This is NOT an equivalent statement… • SELECT DISTINCT pub_nameFROM publishers p, titles tWHERE p.pub_id = t.pub_idAND type < > 'business'; • Why?

  20. NOT not equal < > • This last statement returns any publisher who is publishing a book that is not a ‘business’ type book. Every publisher has at least one book that is some other type than business, so all publishers are returned by the statement.

  21. Comparison Operator Subqueries • Comparison Operators (=, >, <, etc.) • Must resolve to a single value… unless they are modified with the ANY or ALL keyword

  22. Example • SELECT au_lname, au_fnameFROM authorsWHERE city = (SELECT city FROM publishers WHERE pub_name = 'New Age Books');

  23. Exercise • List the author’s name who appear as the third author on a book. (Note: there's is only one)

  24. Answer • SELECT au_lname, au_fnameFROM authorsWHERE au_id = (SELECT au_id FROM titleauthors WHERE au_ord = 3);

  25. Using Aggregate Functions • Aggregate Functions always return a single value for a set of rows • SELECT titleFROM titlesWHERE advance/ytd_sales > (SELECT MAX(price) FROM titles);

  26. GROUP BY and HAVING • Don't use GROUP or HAVING unless you know that they will return a single value! • SELECT title, typeFROM titlesWHERE price > (SELECT MIN(price) FROM titles GROUP BY type HAVING type = 'trad_cook');

  27. Exercise • List the titles and advances where the advance is equal to the average advance of the popular computing books.

  28. Answer • SELECT title, advanceFROM titlesWHERE advance = (SELECT AVG(advance) FROM titles WHERE type = ‘popular_comp’);

  29. Modified Comparison Operators • You may modify a comparison operator with the ANY or ALL keyword • Allows the Inner query to return more than one value

  30. Example • SELECT au_lnameFROM authorsWHERE city > ALL (SELECT city FROM publishers);

  31. ALL means All • > ALL means greater than all the values returned from the Inner query • SELECT priceFROM titlesWHERE price > ALL (1, 2, 3) • Means where price is greater than 3. • > ALL means greater than the maximum value

  32. ANY means Some • > ANY means greater than at least one value returned from the query • SELECT priceFROM titlesWHERE price > ANY (1, 2, 3) • Means where price is greater than 1. • ANY mean greater than the minimum value

  33. Confusing SQL with English • In English: • Which books commanded an advance greater than any book published by Algodata Infosystems? • In SQL: • Which books have an advance greater than the largest advance paid by Algodata Infosystems? • Must use the ALL keyword, not the ANY

  34. Example • SELECT au_lnameFROM authorsWHERE city > ANY (SELECT city FROM publishers);

  35. Comparing IN, ANY and ALL • The '= ANY' operator is equivalent to IN • SELECT au_lname, au_fnameFROM authorsWHERE city IN (SELECT city FROM publishers); • SELECT au_lname, au_fnameFROM authorsWHERE city = ANY (SELECT city FROM publishers);

  36. < > ANY is different than NOT IN • SELECT DISTINCT au_lname, au_fnameFROM authorsWHERE city < > ANY (SELECT city FROM publishers); • What does this find?

  37. Discussion • This returns all authors because every author does not in at least one of the citys that a publishers is headquartered in.

  38. Correctly Done • SELECT au_lname, au_fnameFROM authorsWHERE city NOT IN (SELECT city FROM publishers); • Also, the < > ALL would have worked

  39. Exercise • List the sales order number, store id, and sale date that have a sale date later than any shipped date for books that cost $19.99.

  40. Answer • SELECT sonum, stor_id, sdateFROM sales, salesdetailsWHERE sdate > date_shippedAND title_id IN (SELECT DISTINCT title_id FROM titles WHERE price = 19.99);

  41. Correlated Subqueries • Inner query references the outer query one row at a time for each row in the Outer query.

  42. Example • SELECT pub_nameFROM publishers pWHERE 'business' IN (SELECT type FROM titles WHERE pub_id = p.pub_id); • Inner query needs values from the Outer query, then passes result to the Outer query

  43. EXISTS Correlated Subquery • EXISTS tests for the presence or absence of the "empty set" of rows. • If Inner query returns at least one row… • EXISTS will succeed • NOT EXISTS will fail • If Inner query returns no row… • EXISTS will fail • NOT EXISTS will succeed

  44. Example • SELECT DISTINCT pub_nameFROM publishers pWHERE EXISTS (SELECT * FROM titles WHERE pub_id = p.pub_id AND type = 'business');

  45. Easier to use Join • SELECT DISTINCT pub_nameFROM publishers p, titles tWHERE t.pub_id = p.pub_idAND type = 'business';

  46. Example - Non-Existence • SELECT titleFROM titlesWHERE NOT EXISTS (SELECT title_id FROM salesdetails WHERE title_id = titles.title_id);

  47. Intersection & Difference • EXISTS and NOT EXISTS can be used for two set theory operation • Intersection: Results are returned for the elements that belong to both original sets • Difference: Results are returned for the elements that belong only to the first of the two sets.

  48. Example - Intersection • SELECT DISTINCT cityFROM authorsWHERE EXISTS (SELECT * FROM publishers WHERE authors.city = publishers.city); • SELECT DISTINCT city FROM authors WHERE city IN (SELECT city FROM publishers WHERE city = authors.city);

  49. Example - Difference • SELECT DISTINCT cityFROM authorsWHERE NOT EXISTS (SELECT * FROM publishers WHERE authors.city = publishers.city);

  50. Subquery Rules • The select of an Inner query using an 'IN' or a comparison operator can only have one expression or column name • Select list of an Inner query should be * when using EXISTS • Subqueries using unmodified operators must return a single value • Subqueries cannot use ORDER BY

More Related