Joins Involving Inline Views

As we already mentioned in this chapter, a query that involves table joins could be quite complicated. You can join tables with other tables, views, or any other RDBMS-specific objects you can select rows from to produce the resulting set.

Another type of object that can participate in a join is an inline view (which simply is a nested query in the FROM clause).

Note

An inline view could be useful to produce a resulting set that is used in only one single query as an alternative to creating a regular view. Unlike regular views, inline view definitions do not exist in the database information schema and require no maintenance at all. You could consider using inline views in your queries if you know for sure you (or somebody else) are not going to use its results anywhere else.

The query below returns phone numbers for salesmen who have associated customers with more than five orders using inline query results in SQL99 standard join. The inline view counts the number of orders per customer, limits the result with only those who have more than five orders, and returns the salesmen id's for those customers. The results are then joined with SALESMAN table to get the appropriate salesmen names; the resulting set in its order is joined with PHONE table to retrieve the salesmen phone numbers:

SELECT s.salesman_name_s,
       p.phone_phonenum_s
FROM   salesman s 
    JOIN
       (SELECT   cust_id_n, 
                 cust_salesmanid_fn,
                 COUNT(ordhdr_nbr_s) cnt 
        FROM     customer
           JOIN 
                 order_header 
           ON    cust_id_n = ordhdr_custid_fn
        GROUP BY cust_id_n,
                 cust_salesmanid_fn
        HAVING  COUNT(ordhdr_nbr_s) > 5) c
    ON         s.salesman_id_n = c.cust_salesmanid_fn
    JOIN       phone p
    ON         p.phone_salesmanid_fn = s.salesman_id_n
WHERE          p.phone_type_s = 'PHONE'
   
SALESMAN_NAME_S          PHONE_PHONENUM_S
------------------------ --------------------
HUNTER COMPONENTS        (717) 555-5479
HENERY INCORPORATED      (814) 555-0324
   
  2 record(s) selected.

Note that in this example you have joins in both the main query and the nested query (inline view); the joins in the inline view are performed first and the resulting set is later used in the main query.