SQL and Relational Theory, 2nd Edition

EXAMPLE 8: USING COUNT

Now, there’s still a little more to be said about the previous example. Let me state the query again: “Get suppliers such that every part they supply is in the same city.” Here’s yet another possible natural language interpretation of this query:

Get suppliers SX such that the number of cities for parts supplied by SX is less than or equal to one.

Note that “less than or equal to,” by the way—“equal to” alone would correspond to a different interpretation of the query (right?). Logical formulation:

     { SX } WHERE COUNT ( PX.CITY WHERE EXISTS SPX
                           ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) ) ≤ 1

This is the first example in this chapter to make use of an aggregate operator. As I think you can see, however, the mapping is quite straightforward. An equivalent SQL formulation is:

     SELECT *
     FROM   S AS SX
     WHERE  ( SELECT COUNT ( DISTINCT PX.CITY )
              FROM   P AS PX
              WHERE  EXISTS ( SELECT *
                              FROM   SP AS SPX
                              WHERE  SPX.SNO = SX.SNO
                              AND    SPX.PNO = PX.PNO ) ) <= 1

The result is as shown under Example 7. However, I remind you from the previous chapter that as a general rule it’s wise, for performance reasons, to be careful over the use of COUNT; in particular, don’t use it where EXISTS would be more logically correct.

Here are some questions for you: First, given the foregoing SQL formulation of the query, is that DISTINCT in the COUNT invocation really necessary? Second, try to formulate the query in terms of GROUP BY and HAVING. If you succeed, what were the logical steps you went through ...