6.1 Pick Standard Constructions over Proprietary Constructions 107

SELECT O1.order_nbr,
FROM Orders AS O1,
OrderDetails AS D1
WHERE O1.order_nbr = D1.order_nbr
AND D1.dept = 'mens wear';

New style:

SELECT O1.order_nbr,
FROM Orders AS O1
INNER JOIN
OrderDetails AS D1
ON O1.order_nbr = D1.order_nbr
AND D1.dept = 'mens wear';

Mixed style:

SELECT O1.order_nbr,
FROM Orders AS O1
INNER JOIN
OrderDetails AS D1
ON O1.order_nbr = D1.order_nbr
WHERE D1.dept = 'mens wear';

Exceptions:

The infixed join operators must be used if there is an OUTER JOIN in
the FROM clause. The reason is that the order of execution matters with

OUTER JOINs, and you can control it better with parentheses and
predicates if they are all together.
As a rule of thumb, when you have a FROM clause with five or more
tables in it, the traditional syntax is probably easier to read than trying to
visually match the ON clauses to the proper tables and correlation
names. This rule of five is mentioned in other places as a limit on human
data processing ability.

6.1.3 Use ISO Temporal Syntax

Rationale:

The only display format allowed for temporal data in Standard SQL is
based on ISO-8601, and it is the “yyyy-mm-dd hh:mm:ss.sssss” style.
The Federal Information Processing Standards (FIPS) require at least five
decimal places of precision in the seconds. Anything else is ambiguous

108 CHAPTER 6: CODING CHOICES

and not acceptable if you want to work with other software that follows
ISO standards.
Standard SQL defines a minimal set of simple temporal math
operators. All of them are available in all SQL products, but the syntax
varies. For example, in the T-SQL dialect, the function call “DATEADD
(DD, 13, birthdate)” adds “13” days to the date in birthdate. The
Standard SQL syntax for the same calculation is “birthdate + INTERVAL
‘13’ DAY” instead.
You can set the display to ISO-8601 in every SQL product, and you
can do 99.99 percent of your temporal work without any proprietary
temporal functions. The problem is that porting code can be a bother.

You need to make a set of notes about any differences in your dialect and
the standard.

Exceptions:

None. Display formatting is always done in the client layer of a tiered
architecture. This is a basic programming principle and has nothing to
do with SQL per se. Failure to follow this principle is usually the result
of a newbie who came to SQL from a traditional monolithic language
with a strong coupling between the application, the display, and the file
system.

6.1.4 Use Standard and Portable Functions

Rationale:

Standard SQL is not a computational language, so it does not have the
function library of FORTRAN or a statistical package. SQL is not a text
manipulation language, so it does not have the function library of ICON
or Snobol. All you have is simple four-function math and basic string
operators in SQL-92. Vendors have always provided more than just the
basic operators, so you can write portable code that assumes other math
and string functions. The most common extra math functions are
modulus, rounding and truncation, powers, and logarithms. The most
extra common string functions are replacement, reversal, and repetition.

Exceptions:

If your dialect has a function built into it, which would require a huge
amount of code to implement or a really long running time, then use the

proprietary function and comment it for porting.

6.2 Pick Compact Constructions over Longer Equivalents 109

6.2 Pick Compact Constructions over Longer Equivalents

“Entia non sunt multiplicanda praeter necessitatem.” (No more things should
be presumed to exist than are absolutely necessary.)

—William Occam (c. 1280–1349).

“Everything should be made as simple as possible, but not simpler.”

—Attributed to Albert Einstein
Writing code in as short, clear, and compact a form as possible is just
good software engineering for any programming language. Modules that
clearly do one function are easier to modify and to understand. Systems
with fewer modules are easier to maintain.
SQL can replace hundreds of lines of procedural code with a few
statements. You ought to be predisposed to think of short, clean
solutions instead of kludges. However, old habits are hard to kill. Many
newbies still think in terms of logical tests based on Boolean logic and
simple AND-OR-NOT expressions that they know from their first
programming languages.

6.2.1 Avoid Extra Parentheses

Rationale:

Newbies see generated SQL code that has to have extra levels of

parentheses to execute safely and think that this is the way to write code.
Consider this simple query:

SELECT a, b, c
FROM Foobar
WHERE (a = b)
AND (c < 42);

This is not so bad to read, but by the time you have more than five
predicates and useless nesting of parentheses, the code is difficult to
read, and a missing parentheses is a real pain to locate. Let LISP
programmers use them; they really need parentheses.

Exceptions:

Parentheses in moderation can make nested predicates easier to read:

110 CHAPTER 6: CODING CHOICES

SELECT application_nbr
FROM LoanApplications
WHERE years_employed > 5 OR net_worth > loan_amt
AND monthly_expenses < 0.25 * loan_amt
OR collateral > 2.00 * loan_amt AND age > 25
OR collateral > loan_amt AND age > 30
OR years_employed > 2 AND net_worth > 2.00 * loan_amt
AND Age > 21 AND monthly_expenses < 0.50 * loan_amt;

versus:


SELECT application_nbr
FROM LoanApplications
WHERE years_employed > 5
OR (net_worth > loan_amt
AND monthly_expenses < 0.25 * loan_amt)
OR (collateral > 2.00 * loan_amt AND age > 25)
OR (collateral > loan_amt AND age > 30)
OR (years_employed > 2
AND net_worth > 2.00 * loan_amt
AND age > 21
AND monthly_expenses < 0.50 * loan_amt);

In the following section, we will also see how to use a CASE
expression for situations like this one.

6.2.2 Use CASE Family Expressions

The CASE expression is an expression and not a control statement; that
is, it returns a value of one data type. Because SQL is declarative, there is
no flow of control for it to modify, like the CASE statements in other
languages. The number of newbies who do not understand the difference
between an expression and a statement is frightening.
The idea and the syntax came from the ADA programming language.
Here is the formal BNF syntax for a <case specification>:

<case specification> ::= <simple case> | <searched case>
<simple case> ::=
CASE <case operand>
<simple when clause>
[<else clause>]

END

6.2 Pick Compact Constructions over Longer Equivalents 111

<searched case> ::=
CASE
<searched when clause>
[<else clause>]
END
<simple when clause> ::= WHEN <when operand> THEN <result>
<searched when clause> ::= WHEN <search condition> THEN
<result>
<else clause> ::= ELSE <result>
<case operand> ::= <value expression>
<when operand> ::= <value expression>
<result> ::= <result expression> | NULL
<result expression> ::= <value expression>

6.2.2.1 Searched CASE Expression

The searched CASE expression is probably the most-used version of the
expression. The WHEN THEN clauses are executed in left-to-right
order. The first WHEN clause that tests TRUE returns the value given in
its THEN clause, and you can nest CASE expressions inside of each
other. If no explicit ELSE clause is given for the CASE expression, then
the database will insert an implicit “ELSE NULL” clause. If you want to
return a NULL in a THEN clause, you must use a CAST (NULL AS
<datatype>) expression. I recommend always giving the ELSE clause, so
that you can change it later when you find something explicit to return.


6.2.2.2 Simple CASE Expression

The <simple case expression> is defined as a searched CASE expression
in which all of the WHEN clauses are made into equality comparisons
against the <case operand>. For example:

CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'