tailieunhanh - SQL PROGRAMMING STYLE- P22

SQL PROGRAMMING STYLE- P22:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 112 CHAPTER 6 CODING CHOICES WHEN 9 THEN N A ELSE NULL END could also be written as CASE WHEN iso_sex_code 0 THEN Unknown WHEN iso_sex_code 1 THEN Male WHEN iso_sex_code 2 THEN Female WHEN iso sex code 9 THEN N A ELSE NULL END There is a gimmick in this definition however. The expression CASE foo WHEN 1 THEN bar WHEN NULL THEN no bar END becomes CASE WHEN foo 1 THEN bar WHEN foo NULL THEN no_bar error ELSE NULL END The second WHEN clause is always UNKNOWN. Use the simple CASE expression when it is appropriate. Other CASE Expressions The SQL-92 standard defines other functions in terms of the CASE expression which makes the language a bit more compact and easier to implement. For example the COALESCE function can be defined for one or two expressions by 1. COALESCE value exp 1 is equivalent to value exp 1 2. COALESCE value exp 1 value exp 2 is equivalent to CASE WHEN value exp 1 IS NOT NULL THEN value exp 1 ELSE value exp 2 END Pick Compact Constructions over Longer Equivalents 113 Then we can recursively define it for n expressions where n 3 in the list by COALESCE value exp 1 value exp 2 . n as equivalent to CASE WHEN value exp 1 IS NOT NULL THEN value exp 1 ELSE COALESCE value exp 2 . n END Likewise NULLIF value exp 1 value exp 2 is equivalent to CASE WHEN value exp 1 value exp 2 THEN NULL ELSE value exp 1 END Use the most compact form of these CASE expressions and do not expand them out to their definitions. Avoid Redundant Expressions Rationale Most modern SQL engines are pretty smart. This was not always the case so older SQL programmers will sometimes add redundant predicates to a where clause. For example if none of the columns in the table Foobar is NULL-able then given SELECT a b c FROM Foobar WHERE a b AND b c AND c a One of the three search conditions is redundant because it can be deduced from the other two. Redundant predicates only confuse the human readers and do not give information to a good optimizer. 114 CHAPTER 6 CODING CHOICES