SQL Bible [Electronic resources] نسخه متنی

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          (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.)