Fri, 01 Oct 2004

Correct Use of FROM Clauses

There's an interesting article by Stéphane Faroult on ONLamp about when to use subselects rather than JOINs in SQL queries. He says that only tables from which you are returning columns or those tables necessary to join such tables should appear in the FROM clause. All other tables used to limit the rows returned should appear in subselects in the WHERE clause. He rewrites


select distinct a.CUSTOMER_ID, a.CUSTOMER_NAME
from CUSTOMERS a,
     ORDERS    b
where a.ZIP_CODE in ...
  and b.ORDERED_DATE >= ...
  and b.CUSTOMER_ID = a.CUSTOMER_ID
order by a.CUSTOMER_NAME

as


select a.CUSTOMER_ID, a.CUSTOMER_NAME
from CUSTOMERS a
where a.ZIP_CODE in ...
  and a.CUSTOMER_ID in (select b.CUSTOMER_ID
                        from ORDERS b
                        where b.ORDERED_DATE >= ...)
order by a.CUSTOMER_NAME

He also gives another way to rewrite the query as an uncorrelated subquery. (Read the article for an explanation of the difference between correlated and uncorrelated queries.)

Having done almost exclusively MySQL development, I have yet to ever use subselects, but I'll definitely keep Faroult's article in mind for when I do.

tech | Permanent Link

The state is that great fiction by which everyone tries to live at the expense of everyone else. - Frederic Bastiat