Portable SQL/Common/Aliases

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 SQLite bails out with a SQL parsing error if tables in these statement types are used:

• INSERT
• UPDATE
• DELETE

Aliases should be avoided for them to ensure portable statements.

---

Back to Overview