Difference between revisions of "Portable SQL/Common/Aliases"
From Linuxnetworks
(content split) |
m (Reverted edits by 193.239.74.228 (Talk) to last version by Nose) |
||
| (8 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
__TOC__ | __TOC__ | ||
| + | == In SELECT statements == | ||
| − | = | + | === Aliasing tables === |
| − | + | ||
| − | == 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: | 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: | ||
| Line 10: | Line 9: | ||
SELECT * FROM table t WHERE t.id = 0 | SELECT * FROM table t WHERE t.id = 0 | ||
| − | == Aliasing columns == | + | === 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: | Contrary to "Aliasing tables" the keyword "AS" between column name and alias is required by most database servers and should always be present: | ||
| Line 16: | Line 15: | ||
SELECT count(*) AS cnt FROM table | SELECT count(*) AS cnt FROM table | ||
| − | == Aliases for computed columns == | + | === 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: | 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: | ||
| Line 22: | Line 21: | ||
SELECT MAX(id) AS maxid FROM table | 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. | |
| − | + | ||
Latest revision as of 09:49, 16 August 2010
Contents
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.
