sqlsql-serverselectalias

How does SQL Server allow multiple of the same alias in the same SELECT statement?

I was doing some queries with Dapper and came across a use case where I needed to return different columns using the same alias to be able to map into different objects with the same property name e.g. Teacher.Name and Student.Name. I was also using a view and it did complain about not allowing columns with the same name. So I aliased the columns to different names in the view and then when doing a select from the view, I aliased them back to the same name and it worked, which I did not expect. I also couldn't find any resources on this.

Example: https://sqlfiddle.com/sql-server/online-compiler?id=4e489d23-e1db-4655-ad8e-493324700b09

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    date DATE
);

-- Insert test data
INSERT INTO users (id, name, date)
VALUES (1, 'John Doe', '2024-01-01'),
       (2, 'Jane Smith', '2024-01-02');

-- Test the SELECT statement with the same alias for different columns
SELECT 
    id, 
    name AS alias_name, 
    date AS alias_name 
FROM 
    users;

-- Clean up
DROP TABLE users;

enter image description here

Solution

  • A top level SELECT SQL query can have multiple columns with the same name, and in fact even columns without a name.

    Typically the query API will allow you to retrieve those columns by name ('id', 'name', 'amount', etc.) or by ordinal (1, 2, 3, etc.). In your case retrieving them by name is not an option. You would need to retrieve them by ordinal. In fact, I actually implemented reading by ordinal in an API I wrote, to avoid confusion with column names, when joining multiple tables that could produce a name collission in the query scope.

    Views are also required to have different names for all the columns that emerge from them, since SQL treats them like database objects similar to tables.

    When it comes to SELECT subqueries:

    1. A table expression subquery (FROM, JOIN, LATERAL, <comma>), however, needs to have clearly identified columns, so they can be used by the outer level scope of the query.

    2. Scalar subqueries (queries that return a single column) are free to have or not to have a column name.

    3. Assymetric queries (ANY/ALL modifiers) are free to have or not to have identical/same names; existence queries ([NOT] EXISTS, [NOT] IN) fall into the same category.