Portable SQL/Common/Aliases
Contents
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? 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
