1 / 93

SQL Unit 8 Subqueries with IN, Joins, and Other Topics

SQL Unit 8 Subqueries with IN, Joins, and Other Topics. Kirk Scott. 8.1 Queries That Can Be Done with a Join or with IN 8.2 Queries That Can Easily Be Done with a Join But Not Done with IN 8.3 It is Not Clear that NOT IN Queries Can Be Done with a Join 8.4 Mixing IN Queries and Joins

anevay
Download Presentation

SQL Unit 8 Subqueries with IN, Joins, and Other Topics

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. SQL Unit 8Subqueries with IN, Joins, and Other Topics Kirk Scott

  2. 8.1 Queries That Can Be Done with a Join or with IN • 8.2 Queries That Can Easily Be Done with a Join But Not Done with IN • 8.3 It is Not Clear that NOT IN Queries Can Be Done with a Join • 8.4 Mixing IN Queries and Joins • 8.5 Doubly Nested IN Queries • 8.6 Subqueries with Operators • 8.7 Counting the Number of Distinct Values • 8.8 Finding Aggregates of Aggregates with IN Queries

  3. 8.1 Queries That Can Be Done with a Join or with IN

  4. In the kinds of IN queries considered so far, what is happening is a match on equality between the corresponding fields of the inner and outer queries. • This means that it is possible to get the same results with a join query.

  5. For example, for this IN query: • SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale)

  6. You would get the same results by doing this join query: • SELECT DISTINCT name • FROM Salesperson, Carsale • WHERE Salesperson.spno = Carsale.spno

  7. Keep in mind that the IN query will eliminate duplicate spno values. • Assuming that there are no duplicate salesperson name values, then the join query with DISTINCT will give exactly the same results as the query with IN. • If there are duplicate salesperson names, then the queries would not produce exactly the same results.

  8. Here is another example of an IN query: • SELECT stickerprice • FROM Car • WHERE vin IN • (SELECT vin • FROM Carsale)

  9. You could do this with the following join query: • SELECT stickerprice • FROM Car, Carsale • WHERE Car.vin = Carsale.vin

  10. Keep in mind that since vin is the primary key of the Carsale table, the IN would not have the effect of squeezing out duplicate vin values. • There are none to squeeze out. • However, various different cars may have the same stickerprice, so there could be duplicate stickerprice values in the IN query results.

  11. If that were the case, then putting DISTINCT in the join query would make sure that the two queries gave different results. • The most likely equivalent join query would not have DISTINCT in it.

  12. 8.2 Queries That Can Easily Be Done with a Join But Not Done with IN

  13. There are some things you can do with a join query that you can't do with an IN query. • The join query shown below includes fields from two tables in its results: • SELECT stickerprice, salesprice • FROM Car, Carsale • WHERE Car.vin = Carsale.vin

  14. An IN query can only show fields from the outer table in the results. • The IN query shown below attempts to select fields from both the inner and outer tables, but the query is incorrect. • It will not show the desired results: • SELECT stickerprice, salesprice • FROM Car • WHERE vin IN • (SELECT vin • FROM Carsale)

  15. You can't use a field in the outer query that only exists in the table of the inner query. • The parentheses serve as a barrier. • The field salesprice does not exist in the outer query.

  16. If you typed this query in and tried to run it, you would get a text box prompting you for the value of the variable salesprice. • The outer part of the query does not recognize this as the name of the field in the Carsale table in the inner query.

  17. The reason the query doesn’t work has to do with the meaning of the keyword IN. • We have seen inline views, which are a kind of subquery, before. • In that kind of query it was possible to select a field from a table in the subquery.

  18. The difference with an IN query is this: • The only purpose served by the subquery is to present a set. • The outer query asks whether a value is in the set. • In effect, the IN can only return a yes or a no. • It cannot return an actual value from the inner query.

  19. 8.3 It is Not Clear that NOT IN Queries Can Be Done with a Join

  20. Here is a NOT IN query that finds the stickerprices of cars that haven't sold: • SELECT stickerprice • FROM Car • WHERE vin NOT IN • (SELECT vin • FROM Carsale)

  21. How to do this as a join is not readily apparent, so the NOT IN syntax is preferable. • Shown below is an attempt to get the same results with an inequality join query. • This will not give the same results as the join query: • SELECT stickerprice • FROM Car, Carsale • WHERE Car.vin <> Carsale.vin

  22. Not only does this query not give the same results as the NOT IN query; • it is highly likely that its results are not useful at all. • The query matches every car in the Car table with every car in the Carsale table that does not have the same vin. • This is a case where the results are the Cartesian product minus the equijoin, and it is hard to imagine a situation where they would be desirable.

  23. The previous two points, summarized, are: • With joins, you can pull fields from the inner table, but you can’t do that with IN. • With IN, you can do NOT IN, but the join equivalent is not apparent.

  24. 8.4 Mixing IN Queries and Joins

  25. You may wonder whether it is possible to get values from the inner table with IN queries. • The answer is yes, because it is possible to mix joins and IN. • Suppose you would like a query to return the names of salespeople and the salesprices of cars they sold to customers who live in Anchorage.

  26. Salesperson name and salesprice come from the Salesperson and Carsale tables, respectively. • The proposed query also has a condition that applies to the Customer table. • All together, the query would depend on 3 tables.

  27. Because there are three tables, if this were to be done as an IN query, it would have to be doubly nested. • This is covered in the next section. • Since the query needs fields from more than one table, it wouldn’t be possible to write it using only IN anyway.

  28. It could be written as a three-way join. • That might be the simplest option. • However, it could also be written as a query containing both an IN and a join. • That is the point of this section. • You can mix and match IN and join as needed and desired. • Here is the query written in this way.

  29. SELECT name, salesprice • FROM Salesperson, Carsale • WHERE Salesperson.spno = Carsale.spno • AND custno IN • (SELECT custno • FROM Customer • WHERE city = 'Anchorage')

  30. 8.5 Doubly Nested IN Queries

  31. The example of the previous section illustrated a query that could be done with a 3-way join or a single join combined with an IN subquery. • Suppose that fields from only one table were wanted in the results of a query that depended on three tables. • It would be possible to do this with a doubly nested IN query.

  32. Once you start nesting queries and mixing IN and NOT IN, analyzing what the queries mean can get complex. • The following examples will be based on the general question of identifying salespeople according to whether they sold to customers in Anchorage or outside of Anchorage. • The Venn diagram shows the possibilities.

  33. Salespeople who sold to customers in Anchorage. Salespeople who sold to customers outside of Anchorage.

  34. 1. The crescent moon shaped area on the left represents those salespeople who sold only to customers in Anchorage. • 2. The whole circle on the left represents those salespeople who sold to at least one customer in Anchorage, although they may also have sold to a customer outside of Anchorage.

  35. 3. The crescent moon shaped area on the right represents those salespeople who sold only to customers outside of Anchorage. • 4. The whole circle on the right represents those salespeople who sold to at least one customer outside of Anchorage, although they may also have sold to a customer in Anchorage.

  36. 5. The lens shaped area in the middle, the intersection, represents those salespeople who sold to customers both in Anchorage and outside of Anchorage. • In practice it seems reasonable to suppose that that would be the largest subset. • It is only in the interests of clear labeling that it appears smallest in the Venn diagram.

  37. The following example query finds the names of salespeople who sold cars to customers who live in Anchorage.

  38. SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale • WHERE custno IN • (SELECT custno • FROM Customer • WHERE city = 'Anchorage'))

  39. In terms of the Venn diagram, it selects the whole circle on the left.

  40. Salespeople who sold to customers in Anchorage. Salespeople who sold to customers outside of Anchorage.

  41. Recall that nested queries are executed from the inside out. • If you choose to write a query like this one, you might find that it is most logical to work your way from the inside out. • You could also get the same results using a three-way join, something you already know how to do.

  42. It is possible to negate the conditions of a doubly nested IN query. • Once you start doing this, the queries become harder to understand. • Here is an example with a NOT IN query nested inside an IN query. • It makes sense to show the SQL first. • Then the explanation will be given.

  43. SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale • WHERE custno NOT IN • (SELECT custno • FROM Customer • WHERE city = 'Anchorage'))

  44. The innermost query finds the sales to customers who are outside of Anchorage. • In combination with this the middle query finds the spno of salespeople who made such sales. • Salespeople who sold both to customers in Anchorage and customers outside of Anchorage because those salespeople sold at least one car outside of Anchorage.

  45. The outer query finds the names of these salespeople. • In other words, this query finds the names of salespeople who sold cars to customers outside of Anchorage. • In addition to selling cars to customers not in Anchorage, they could have sold cars to customers in Anchorage.

  46. The results of this query could only include salespeople who made a sale. • Salespeople who hadn’t made a sale would not be included. • The results of this query are represented by the whole circle on the right hand side of the Venn diagram.

  47. Salespeople who sold to customers in Anchorage. Salespeople who sold to customers outside of Anchorage.

  48. In the next example an IN query is nested inside of a NOT IN query. • Because there is just one NOT in the query overall, superficially it might seem to be similar to the previous example. • However, the results will be different.

  49. SELECT name • FROM Salesperson • WHERE spno NOT IN • (SELECT spno • FROM Carsale • WHERE custno IN • (SELECT custno • FROM Customer • WHERE city = 'Anchorage'))

More Related