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, you 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 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.
Web Hosting
External links for similar resources:
http://stackoverflow.com
External links for similar resources:
Facebook LinkedIn Google Twitter
Database
:: Januari 2015
:: Februari 2014
:: Maret 2014
:: April 2014
:: Mei 2014
:: Juni 2014
:: Juli 2014
:: Agustus 2014
:: September 2014
:: Oktober 2014
:: November 2014
:: Desember 2014
:: Dinar Emas
:: Dirham Perak
:: Khamsa Perak
:: Lantakan 5g
:: Lantakan 10g
:: Lantakan 25g
:: Lantakan 50g
:: Lantakan 100g
:: Hello Kitty
:: Database Forum
:: Subquery Alias
:: Char Padding
:: UIB
:: JumpShip.Asia
:: Login
Database Forum :: Oracle, PL/SQL, SQL Server, Transact-SQL, T-SQL Tauke Tas :: Tas dan Dompet Wanita Logam Mulia :: Dinar Emas, Dirham Perak, Khamsa Database Forum :: Oracle, PL/SQL, SQL Server, Transact-SQL, T-SQL UIB, Universitas Internasional Batam,  Budi Prayitno Login, Logout
Images contained in this website may be subject to copyright and may not be illegally distributed without proper permission from their each respective owner.
Information contained in this website is for general information purposes only and subject to change without prior notice.
Copyright © 2014 Pernak Pernik™, All Rights Reserved.