|
|
|
|
Alias Name for Subquery in SQL Server
Below is basic SQL statement you can use to retrieve data from a table in any Database server:
SELECT * FROM MyTable;
To nest SQL statement above as subquery within FROM clause of another SQL statement, Oracle users will simply find this working:
SELECT * FROM (SELECT * FROM MyTable);
Meanwhile, SQL Server users will find error below due to execution of SQL statement above:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ';'.
To solve this, SQL Server users must explicitly provide an alias name for subquery in the FROM clause as below:
SELECT * FROM (SELECT * FROM MyTable) AS MySubQuery;
It does not stop here. SQL Server users will find another sample of query below not working despite alias name has already been defined for the subquery:
SELECT * FROM (SELECT 'Foo') AS MySubQuery;
Execution of the above query in SQL Server will result in following error message:
Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'MySubQuery'.
It is basically similar issue of aliasing, only on different part of the statement. Every pseudo-column within the subquery should have an alias name as well:
SELECT * FROM (SELECT 'Foo' AS Bar) AS MySubQuery;
Conclusion, Oracle will automatically set an alias name for its users while SQL Server users are required to explicitly define an alias name for each expression which came not from literal columns or tables.
|
|
|
|
|
|
|
|
|
|
