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 (541) 555-8979 WILE ELECTROMATIC INC. (541) 555-3246 WILE ELECTROMUSICAL INC. (503) 555-0502 WILE ELECTRONICS INC. (609) 555-4091 WILE SEAL CORP. (909) 555-9957 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.)