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

     SELECT sic    /* "SELECT item commalist" */
     FROM   T1
     WHERE  [ NOT ] EXISTS ( SELECT *
                             FROM   T2
                             WHERE  T2.C = T1.C
                             AND    bx )

can be transformed into

     SELECT sic
     FROM   T1
     WHERE  T1.C [ NOT ] IN ( SELECT T2.C
                              FROM   T2
                              WHERE  bx )

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.