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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIn 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.
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.
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.
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,