Multitable Joins with Correlated Queries

One important rule to remember is never to combine the new syntax with the old one within a single query. First, such syntax may not work properly, and also it would definitely look confusing.

However, if you need to create a correlated query, the SQL99 syntax simply will not work, so the general recommendation is to either use the old syntax or replace correlated query with something else.

The following statement uses a correlated query to retrieve all customer names and phone numbers for customers who have orders:

SELECT   DISTINCT c.cust_name_s,           p.phone_phonenum_s FROM     customer c,          phone p WHERE    c.cust_id_n = p.phone_custid_fn AND EXISTS (SELECT *             FROM order_header oh             WHERE oh.ordhdr_custid_fn = c.cust_id_n) AND      p.phone_type_s = 'PHONE'     CUST_NAME_S                PHONE_PHONENUM_S -------------------------- ---------------- WILE BESS COMPANY          [phone] WILE ELECTROMATIC INC.     [phone] WILE ELECTROMUSICAL INC.   [phone] WILE ELECTRONICS INC.      [phone] WILE SEAL CORP.            [phone]       5 record(s) selected.

The equivalent query that does not involve correlated queries is

SELECT  DISTINCT c.cust_name_s,           p.phone_phonenum_s  FROM     customer c     JOIN           order_header oh     ON    c.cust_id_n = oh.ordhdr_custid_fn     JOIN           phone p     ON    c.cust_id_n = p.phone_custid_fn WHERE    p.phone_type_s = 'PHONE'

As you can see, it is usually possible to avoid correlated queries in a SELECT statement; UPDATE and DELETE statements could be trickier, especially in Oracle and DB2. (MS SQL has a special FROM clause in UPDATE and DELETE statements as described in Chapter 6.)