Sunday, January 21, 2007

Review Topic: DB2 SQL Unions, Joins

A UNION is a function that combines the results of two or more SELECTS with all columns and all rows encountered. To prevent a misleading result, it is a good idea to use the AS clause. For example, a UNION of a SELECT from an inventory table and a back-order table could imply that parts are on hand when they are not, when the same column (quantity) appears in both tables, unless you rename one of these with an AS.

Many people code INNER JOINs with implicit syntax (coding the actual rule) but it is better to code with an explicit syntax (INNER JOIN ... ON). Professional coding standards regard INNER JOINS as easier to read. Qualified names can be used with joins.

OUTER JOINS may be full or left or right. A right join can be changed to a left by switching the statement order.

Outer joins are used when it is desirable to keep unmathched rows in a result. For example, a substitute teacher system might want to show inactive or expired subs (those who have accepted no assignments within the reporting period) for investigatory purposes. Call the Full Outer Join a "background investigation." The VALUE or COALESCE function can return a perdetermined string for a null value.

Picture: Railroad on Mississippi coast a few months after Katrina

No comments: