Revision as of 18:49, 10 March 2007

Using aliases

Aliasing tables

Don't use the keyword "AS" between the table name and alias as no DBMS requires it and some don't recognize it. Instead, always write:

SELECT * FROM table t WHERE t.id = 0

Aliasing columns

Contrary to "Aliasing tables" the keyword "AS" between column name and alias is required by most database servers and should always be present:

SELECT count(*) AS cnt FROM table

Aliases for computed columns

The naming of computed columns implemented by the database servers is very different. Some name it like the computational expression ('count(*)'), most often in upper case and sometimes it isn't named at all. Therefore computed columns should be always given a name:

SELECT MAX(id) AS maxid FROM table

Quoting in statements

Quoting values

Quoting string values which should be used inside an SQL statement is essential. But what about numerical values like integers, decimal and floating point values? Many database server support this but MS SQL Server and Sybase ASE don't. Single quotes around numerical values can also cause a performance hit (exceptions seems to be older MySQL servers) and therefore they should be avoided. The correct usage is:

SELECT * FROM table WHERE intcol=1 AND strcol='test'

Nevertheless, also numerical values should be passed to the escape function of the database library first to avoid SQL injection attacks.

Back to Overview