Consider the query “Get names of suppliers who supply both part P1 and part P2.” Here’s a logical formulation:
{ SX.SNAME } WHERE EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = 'P1' ) AND EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = 'P2' )
An equivalent SQL formulation is straightforward:
SELECT DISTINCT SX.SNAME FROM S AS SX WHERE EXISTS ( SELECT * FROM SP AS SPX WHERE SPX.SNO = SX.SNO AND SPX.PNO = 'P1' ) AND EXISTS ( SELECT * FROM SP AS SPX WHERE SPX.SNO = SX.SNO AND SPX.PNO = 'P2' )
Here’s the result:
|
|
|
As you can see, however, this SQL expression involves two correlated subqueries. (In fact, Example 3 involved a correlated subquery also. See Chapter 12 for further discussion.) But correlated subqueries are often contraindicated from a performance point of view, because—conceptually, at any rate—they have to be evaluated repeatedly, once for each row in the outer table, instead of just once and for all. The possibility of eliminating them thus seems worth investigating. Now, in the case at hand (where the correlated subqueries appear within EXISTS invocations), there’s a simple transformation that can be used to achieve precisely that effect. The resulting expression is:
SELECT DISTINCT SX.SNAME FROM S AS SX WHERE SX.SNO IN ( SELECT SPX.SNO FROM SP AS SPX WHERE SPX.PNO = 'P1' ) AND SX.SNO IN ( SELECT SPX.SNO FROM SP AS SPX WHERE SPX.PNO = 'P2' )
More generally, the SQL expression
SELECTsic
/* "SELECT item commalist" */
FROMT1
WHERE [ NOT ] EXISTS ( SELECT * FROMT2
WHERET2
.C
=T1
.C
ANDbx
)
can be transformed into
SELECTsic
FROMT1
WHERET1
.C
[ NOT ] IN ( SELECTT2
.C
FROMT2
WHEREbx
)
In practice, this transformation is probably worth applying whenever it can be. (Of course, it would be better if the optimizer could perform the transformation automatically; unfortunately, however, we can’t always count on the optimizer to do what’s best.) But there are many situations where the transformation simply doesn’t apply. As Example 3 showed, nulls can be one reason it doesn’t apply—by the way, are nulls a consideration in Example 4?—but there are cases where it doesn’t apply even if nulls are avoided. As an exercise, you might like to try deciding which of the remaining examples in this chapter it does apply to.