Difference between revisions of "Portable SQL/Common/Aliases"

Latest revision as of 09:49, 16 August 2010

In SELECT statements

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

In other statements

Some database engines doesn't support aliases for tables in other types of statements than SELECT. Namely PostgreSQL and SQLite bail out with a SQL parsing error if aliases for tables are used in these statement types:

• INSERT
• UPDATE
• DELETE

Aliases should be avoided in them to ensure portable statements.