Is there a valid reason to alias a column to the exact same name/case?

Question

I inherited some code like this:

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

Does the Col_2 alias actually accomplish anything, or is it completely unneeded?

Answer 1

In the code as presented in your question, no there is no need but it will not cause any problems. The alias could be there because the column name used to be different and has been renamed, with the references to it being updated by some automated method.

I have sometimes deliberately aliased columns to the same name just to keep line syntax alignment when working with many columns some of which are aliased and some of which are not like so:

SELECT tbl1.col1          AS column1
     , tbl1.anothercolumn AS column2
     , tbl1.column3       AS column3
     , tbl2.somevalue     AS column4
FROM   ...

Here column 3 has an alias specified that is does not need as the output would have the desired name anyway.

Answer 2

I think there's a valid use case if two tables share a column name. For instance, in the Stack Overflow schema:

SELECT TOP 10
       p.Id, p.CreationDate, p.OwnerUserId, p.Score, 
       c.CreationDate, c.Score
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON p.Id = c.PostId;

Both Posts and Comments have columns called CreationDate and Score. If I return those results to an end user, they can't see the table aliases, so knowing which belongs to Posts and which to Comments is impossible.

I could alias things to make that more clear.

SELECT TOP 10
       p.Id, p.CreationDate AS CreationDate, p.OwnerUserId, p.Score AS Score, 
       c.CreationDate AS CommentCreationDate, c.Score AS CommentScore
FROM   dbo.Posts AS p
JOIN   dbo.Comments AS c
    ON p.Id = c.PostId;

Now, aliasing the columns as their names doesn't achieve anything other than point out to future developers that you're aware of the duplication of column names. This can help provide clarity and a little self-documentation with long select lists and unformatted code.

Answer 3

The only reason is for Proc readability

SELECT
   [First Name] = a.[FirstName],
   [Middle Name]= a.[Middle Name],
   [Last Name]  = a.[LastName],
   [DOB]        = b.[DOB]
...

looks cleaner and is easier to quickly read then

SELECT
   [First Name] = a.[FirstName],
   a.[Middle Name],
   [Last Name]  = a.[LastName],
   b.[DOB]
...

especially if you have large case statements or Data manipulations.

But overall I think its mostly personal preference.

Edit: My example applies to Microsoft sql-server, which this question was tagged with when I answered.

Answer 4

There is no need to alias a column name unless you are changing it to something besides what the column name in the database is.

This select

SELECT
  Col_1,
  Col_2 Col_2,
  Col_3
...

And this selct

SELECT
  Col_1,
  Col_2,
  Col_3
...

Will both return the same column names to the end user so in this case there is no need to add the alias as it will have no impact on the returned results or column names.

Regarding duplicate column names as brought up by sp_BlitzErik this will depend on your rdms software as depending on what you are using it will automatically change duplicate column names so that the result set does not have any duplicate column names. For example Oracle (11g) adds an _1, _2 for the 2nd, 3rd column and follow the pattern for each duplicate column,