Archive for the ‘sql’ Category


CODI 2006: SQL tips from John Craig at alpha-G


First: don’t use SQL Advantage. Instead, use WinSQL (free,, jEdit (free,; plus an SQL query tool, since jEdit is only an editor).

Use indenting to show the structure of your code.

Better: align everything so that all the arguments of one verb begin on the same column. Put commas, etc. at the front of the line; it’s easier to spot a missing comma.

Lower-case is easier to use. Avoid SELECT; use select instead.

Comment your end statements:

while (...) begin
end -- while

“Write your code for a human, not for a computer. Sure, the computer runs it, but a computer is dumb as a post!”

Use the human brain’s propensity for recognizing visual patterns; use formatting to your advantage.

Always use full names for columns. It’s fine to use o.entry_code instead of owner.entry_code, but don’t just use entry_code, even if it’s unique within the tables in the select.

If you use t. for title in one part of the script, don’t use t. to mean tags in another part. It’s legal but confusing.

Within a where clause, group your qualifiers together: all the b.---s first, then the bc.---s, then the i.---s…

Comment, comment, comment. Formatting is immanent commenting. Syntax highlighting helps, too. Use both single-line (--) and multi-line (/* */) comments.

Use a universal syntax. Don’t rely on Sybase commands; Sybase isn’t supported in Horizon 8.0. Keep it standard.

Use the join keyword for joins:

join borrower_barcode bb on b.borrower# = bb.borrower#


left outer join borrower_barcode bb on b.borrower# = bb.borrower#

Use a join for each table being joined. Use parentheses around each on-clause for clarity.

Use is null instead of = ''. An empty string is not null, it’s a string of length 0.

Use “” only for column aliases (select i.location as "item location"). Elsewhere, use single quotes: where collection='flafic'

Use optional prepositions: delete from table, not delete table. It may not be optional on other platforms.

The only appropriate time to use = null is in a set statement: set update_user = null. In this case, = is an assignment operator, not comparison.

Instead of concatenating with +, use ||, like so: select location || ' ' || itype

All non-function columns in the select clause must be present in a group by clause. You have to enumerate them all if you use group by. The exceptions are count, sum, max, etc.

Don’t use tinyint. Instead of convert, use cast (expression as type). Instead of select into, use create table and then insert. Use set, not select, for assigning values. Use flow control: if, while, for, break, end. But DB2 restricts these in some contexts.

Instead of temp tables, make a local database.

Oracle doesn’t support exists.

set rowcount isn’t standard, but there’s no standard replacement.

Instead of isnull, use nullif().

Pattern matching: % is portable, ranges aren’t. Some DBs support regex.

Triggers and stored procs aren’t standard.