Thursday, February 15, 2007
Review topics: SQL wrap-up
ANSI SQL sources seem to indicate that UNION functions (which combine two or more result sets) are supposed to eliminate duplicates. However, with DB2, Murach indicates that it may or may not, depending on how it is coded, and a UNION may or may not provide acceptable performance in production. In DB2, UNIONs with unnamed intermediate columns apparently retain the names in the first SELECT, which can give misleading results.
With the ORDER BY, the acceptable options are ASC and DESC.
Column names should always be separated by commas.
A JOIN, compared to a UNION, brings together two or more tables as matched (with unmatched row-column values for FULLs) into a single result set.
A self-join relates to a table joined to itself.
A subquery must always be coded in parentheses.
When multiple tables are "joined" in a query without a join condition, a Cartesian Product results.
A subquery after an IN list can retain only one column.