?This week's SQL article is a collection of tips and techniques for writing SQL.

The tips are divided into these categories:

* Syntax
* More Syntax
* Performance
* Design

Thesyntax tips cover reserved words, NULLs, strings, parentheses, and INlists. You'll also learn what's wrong with "select star", why JOINsyntax is better, and when to write RIGHT OUTER JOINs (hint: never).

Theperformance tips barely scratch the surface (database performance is acareer unto itself), but they include the importance of indexes and howto make sure you don't sabotage your own indexes.

The design tips include advice about first normal form, using simple datatypes, and autonumbers.

Ifyou like these tips (or not), if you want to see more, or if you haveany feedback on them at all, please post a comment in the discussionforum for this article.
Reserved words

Do not define a tableor column name using a reserved word like DATE, ORDER, GROUP, and soon. You are only asking for syntax errors ("... and it annoys thepig"). You will constantly have to "escape" the name. See Reservedwords for various databases.

For the same reason, don't usespecial characters or spaces in your table or column names, either.There is really nothing to be gained by it.
Strings are not numbers

Don'tput quotes around numbers, because it turns them into strings. Don'tuse strings for numeric columns (or numbers for string columns). Mostdatabases will give you a syntax error (e.g. "data type mismatch").MySQL, in one of many such non-standard behaviours, happily tries to dowhat you ask. However, it takes extra processing time to do theconversion. So instead of:

insert
into Posts
( ..., userid, ... )
values
( ..., '37', ... )

Write this instead:

insert
into Posts
( ..., userid, ... )
values
( ..., 37, ... )
NULL is a keyword

Do not enclose it in quotes.

Andwhile we're on the subject of NULL, NULL is not the same thing as anempty string. An empty string is equal only to an empty string, but notto NULL. In fact, nothing is equal to NULL, not even another NULL.Don't you just love it?
Use parentheses

Whenever you have amix of ANDs and ORs, do yourself a favour, use parentheses to ensureyou get the logic you want. ANDs take precedence over ORs, but it'salways better to write the parentheses than rely on this. For example,consider:

select S.foo, O.bar
from sometable as S
, othertable as O
where S.id = O.Sid
and S.grp = 'C'
or S.cls = 4

What this says is:

WHERE x AND y OR z

Because ANDs take precedence over ORs, this is evaluated as:

WHERE (x AND y) OR z

Clearly, the last condition by itself may easily be true for combinations of S.id and O.Sid which are not equal.

What you want is:

WHERE x AND (y OR z)

Thus you need to code the parentheses:

select S.foo, O.bar
from sometable as S
, othertable as O
where S.id = O.Sid
and (
S.grp = 'C'
or S.cls = 4
)

FOR FULL ARTICLE .. CLICK HERE!!!