SQL Unit 9 Correlated Subqueries

1. SQL Unit 9Correlated Subqueries Kirk Scott

4. 9.1 Correlated Subqueries • 9.2 EXISTS • 9.3 NOT EXISTS • 9.4 Double NOT EXISTS

5. 9.1 Correlated Subqueries

6. 1. Recall how subqueries with IN work. • There is an inner query and an outer query. • The inner query is run first, producing a set of results. • The outer query is run second, and the results it produces depend a row-by-row, yes or no test, of whether a field value is in the results of the inner query.

7. A row would be included in the outer query results only if the value of one of its fields matched a value in the results of the inner query. • The connection between the two queries depends on the matching of the field in the WHERE clause of the outer query with the field in the SELECT clause of the inner query.

8. Here is an example that finds the names of salespeople who sold cars: • SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale)

9. 2. The following example illustrates the syntax of a correlated subquery. • There is a join in the inner query that involves a field from the table of the inner query and a field from the table of the outer query. • This is what is meant by correlation:

10. SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale • WHERE Salesperson.spno = Carsale.spno)

11. You would never actually write a query like this intentionally, because the correlation in this case is redundant. • This query gives exactly the same results as the previous example. • However, the query is syntactically correct and it illustrates some of the important aspects of correlation.

12. Consider the join. • Recall that for inline views, the outer query may make use of fields defined in the inner query. • On the other hand, for queries with IN, the outer query can’t make use of fields defined in the inner query.

13. In a correlated subquery, the fields of the outer table are available to the inner query. • In this example, the inner query can make use of the field Salesperson.spno in the join. • The only thing to keep in mind is that if you are joining on fields that have the same name, then those names have to be qualified with the names of their tables in order to tell them apart.

14. There is another thing to notice about correlated subqueries. • The term correlated means that the inner and outer queries are not independent of each other. • Unlike other nested queries, the inner query does not run to completion first, followed by the outer query. • The inner query cannot run to completion independently of the outer query because the inner query contains a join which involves the outer table.

15. 3. The next example illustrates a correlated subquery which does not have the redundancy of the previous example. • This is a type of subquery with an operator which also includes correlation. • In words, the query will find the names of salespeople who sold at least two cars. • This query works by comparing the results of a correlated subquery using COUNT with the constant value 2:

16. SELECT name • FROM Salesperson • WHERE • (SELECT COUNT(*) • FROM Carsale • WHERE Salesperson.spno = Carsale.spno) >= 2

17. In order to understand what the previous query accomplishes, it might be useful to look at a query that does approximately the same thing using syntax learned earlier. • The following query finds the names of salespeople who sold more than two cars, and also finds the counts of the numbers of cars they sold by doing a join and using GROUP BY with HAVING:

18. SELECT name, COUNT(*) • FROM Salesperson, Carsale • WHERE Salesperson.spno = Carsale.spno • GROUP BY spno HAVING COUNT(*) >= 2

19. Here is an alternative that does approximately the same thing using an inline view. • It only makes use of the Carsale table, so it gives spno instead of name: • SELECT spno, salescount • FROM • (SELECT spno, COUNT(*) AS salescount • FROM Carsale • GROUP BY spno) • WHERE salescount >= 2

20. The inline view plays the role of the table in the FROM clause of the outer query. • The fields of this inline view are available to the outer query. • Since the view is unnamed, a new field, like the COUNT, has to be given an alias if it is to be referred to elsewhere in the query.

21. The examples get a little messy. • It’s clear that there may be more than one way to write a query that accomplishes something more complicated than “SELECT * FROM Table”. • Keep in mind that the point of this section is that one of the alternative ways involves a correlated subquery. • The other ways which we’ve seen before are now just sideshows.

22. 9.2 EXISTS

23. 1. The most common use of correlated subqueries is with the keyword EXISTS. • This is the general form of an EXISTS query: • SELECT blah • FROM blah • WHERE EXISTS • (A correlated subquery goes here. • A join is needed to connect the inner and outer queries.)

24. It may be helpful to compare an EXISTS query with an IN query. • The keyword EXISTS immediately follows WHERE in the query. • No field in the outer query table is specified. • The link between the inner and outer queries arises from the join condition in the inner query alone.

25. 2. Suppose you would like to find the names of salespeople where there exist sales records for those salespeople in the Carsale table. • This would be the solution using the keyword EXISTS: • SELECT name • FROM Salesperson • WHERE EXISTS • (SELECT * • FROM Carsale • WHERE Salesperson.spno = Carsale.spno)

26. The join in the inner query is the only connection between the inner and the outer query. • It is essential. • The WHERE clause of the outer query doesn't specify a field from the outer table. • It goes directly to EXISTS.

27. The inner query does a SELECT *. • There is no need to match fields from the inner and outer table in the SELECT, so you can select anything you want to. SELECT * is used because you only have to type the one symbol, *. • It suggests whole records from the Carsale table, but keep in mind that none of the fields are actually needed in the query.

28. 3. Not surprisingly, what you can do with an EXISTS query you can also do with an IN query or a join query. • Here is the IN query that corresponds to the foregoing EXISTS query: • SELECT name • FROM Salesperson • WHERE spno IN • (SELECT spno • FROM Carsale)

29. Here is the corresponding join query: • SELECT DISTINCT name • FROM Salesperson, Carsale • WHERE Salesperson.spno = Carsale.spno

30. None of the alternatives is either better or worse than the others. • It's personal choice whether to use EXISTS, IN, or a join. • Remember that with the subqueries it's not possible to select fields from the inner table to appear as results in the outer query. • If you want to do that, then you have to use a join.

31. 4. Here is another straightforward example of an EXISTS query. • Like the earlier example, it could also be done with an IN or a join query. • Those alternatives are not shown. • This example is given without further explanation. • You should be able to figure out what it does:

32. SELECT vin, make, model, year • FROM Car • WHERE EXISTS • (SELECT * • FROM Carsale • WHERE Car.vin = Carsale.vin)

33. 5. The following query does not contain a syntactical error, but it is wrong. • It is an EXISTS query without correlation: • SELECT name • FROM Salesperson • WHERE EXISTS • (SELECT * • FROM Carsale)

34. Recall that IN was a set operator. • This example highlights the fact that EXISTS is a kind of logical operator. • Either something exists or it doesn't, either "EXISTS" is true or it's false. • Without the correlation, either Carsale contains records or it doesn't.

35. If it does, then the inner query is true, and the outer query will simply select the names of all salespeople. • If, by chance, the Carsale table contained no records, then the inner query would return false, and the outer query would return nothing, even though there may be salespeople in the Salesperson table.

36. Remember that a join query without a joining condition gives drastically wrong results • —namely a Cartesian product. • An EXISTS query without a joining condition, that is, without correlation, does not give such drastically wrong results. • But if written without a join it is almost certainly not the query that was intended.

37. 6. Just as it was possible to join a table with itself, it's also possible to write a correlated subquery with EXISTS where the table of the inner query is the same as the table of the outer query. • This example will be based on the Salesperson table. • Here are its contents again, as a reminder:

39. Suppose you'd like to find all of the salespeople who are bosses. • You can translate this verbally into an EXISTS query as follows: • Find all salespeople where there exists some other salesperson whose bossno matches the first salesperson's spno.

40. If you're going to open the same table more than once, then you'll need to give it an alias each time. • In this case the aliases will correspond to opening the table as a salesperson who is a boss and opening the table as a salesperson who has a boss.

41. In the query below, the alias A corresponds to opening the table as a salesperson who is a boss. • This is who you're trying to find information about, and it appears in the outer query. • The alias B corresponds to opening the table as an employee. • This appears in the inner query.

42. The joining condition determines which role the A and B play in the query. • Matching on A.spno = B.bossno means that A is playing the role of boss. • Where A and B appear in the query depends on whether you are trying to pull information out of A or information out of B.

43. SELECT * • FROM Salesperson AS A • WHERE EXISTS • (SELECT * • FROM Salesperson AS B • WHERE A.spno = B.bossno)

44. Looking back at the table contents, it's easy to see that the only salesperson who is a boss is Bugs Bunny. • Looking at the logic of the query, spno 333, Bugs Bunny's spno, is the only spno where there are other salesperson records in the table that have this value as their bossno. • As pointed out with an earlier example, in general, it would also be possible to do this query using IN or a join.

45. 7. The last example of a plain EXISTS query is slightly less realistic than some of the others, but it does give a chance to use a non-equijoin as the correlating condition. • Verbally, this query could be described as finding those cars that are so cheap that their stickerprices are lower than the dealercosts of some other cars in the Car table.

46. This is another example where one table, namely the Car table, is opened twice and has to be aliased. • One opening of the table, in the outer query, corresponds to the very cheap cars. • The opening in the inner query corresponds to the ones that are not so cheap:

47. SELECT * • FROM Car AS A • WHERE EXISTS • (SELECT * • FROM Car AS B • WHERE A.stickerprice < B.dealercost)

48. 9.3 NOT EXISTS

49. 1. NOT EXISTS is the negation of EXISTS and its meaning is straightforward. • If you think back to NOT IN, if IN produced a certain set of results, then NOT IN would produce the complement of that set. • Similarly, if EXISTS would hold true for a certain set of tuples, then NOT EXISTS would hold false for that set.

50. Put another way, NOT EXISTS would specify those tuples for which the condition did not hold true. • If you wanted to find the names of salespeople who didn't sell a car, if there were any that didn't sell a car, you could do it this way: