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