SQL same column name allowed
Reason for this problem is:
SELECT custid AAA, companyname AAA FROM Sales.Customers WHERE country = 'USA'
Here, you are assigning column alias at the time of output, so
AAA is as column name(alias actually) attached at the time of returning result, but i guess you will see only 1st column with
AAA other will be removed because of possible confliction during further reference. so here you are not getting error.
SELECT * FROM ( SELECT custid AAA, companyname AAA FROM Sales.Customers WHERE country = 'USA') BBB
Here you got error because, you are selecting records from Inline View with name
BBB, here that Inner Query(inline view) is considered as a Table(for your
SELECT * FROM statement), and as we know - basically Table can not have multiple same column name, because of that you are getting error that BBB has multiple
This can be explained by understanding order of execution of different logical phases of query execution. Query Execution Order MSDN
In SQL Server the order is
FROM > WHERE > SELECT i.e. first FROM clause is executed then WHERE clause and last is the SELECT list.
Now in your first query , all matching rows from table Sales.Customers are fetched and then then afterwards columns specified in
SELECT list are pulled out and then Alias names are applied.
In your second query , the inner query is executed successfully as the first query but when the outer query's
FROM clause tries to fetch columns from resultset returned by inner query , it finds duplicate columns and throws error.