Derived Table Column Alias

Comments: No Comments
Published on: August 18, 2011

By now, you have heard of subqueries.  You have also heard of Common Table Expressions.  I am sure you know what a derived table is and that you get a derived table through either a subquery or CTE.  How familiar are you with the subquery flavor of a derived table though?

I encountered something about derived tables recently that I had never seen, let alone heard of up to that point.  Let’s start with the Microsoft documentation on the topic.  If you browse to this page, you will find a description for column_alias immediately following the description of derived table.  What you don’t get is an example of how it is applicable.  Or do you?

If you look in the example of the derived table on that same page, you will see the following code (formatting added for readability).

[codesyntax lang=”php”]


Here, we can see that column_alias is optionally supplied after the table_alias for the derived table.  In this example, we have supplied two new column aliases called a and b.

If we want, we can take this a step further and see the same sort of example supplied by Sybase.

[codesyntax lang=”sql”]


You can read the documentation about derived table syntax in Sybase, here, if you so desire.  The point of this is to show similar code and documentation between SQL Server and its resuscitated predecessor.

And for grins, you actually have the same sort of optional syntax available for the derived table known as a CTE.  You can see the documentation, from Microsoft, on that here.

So, how do we put this to use?  Well, I am glad you asked that.  I have an example ready to go.

[codesyntax lang=”tsql”]


In this example, I have a derived table implemented through a subquery.  The alias of this derived table is “Latest.”  Note that there is an additional set of parenthesis after that table alias.  Inside this set of parenthesis, you will see a couple of column names.  Those columns are called StudentId and RequestNbr.

Now, I want you to take a look inside that derived table and note the names of the columns I provided in the aliases there.  See how those column_aliases are different than the column_aliases provided after the table_alias?  By looking at the query, can you tell which takes precedence?  Aliases supplied for columns in the optional column_alias outside of the derived table override the column_aliases of those provided inside the derived table.  You can verify that by looking at the join conditions provided after those aliases were defined.

Running this script, you will see it execute without error.  Using this kind of syntax could be useful in certain cases.  I think that it could make finding those column names considerably easier.  It could also help with readability.

Let’s take a quick look at the same kind of setup, but using a CTE instead.

[codesyntax lang=”tsql”]


Note that I moved that entire derived table from subquery to be a new CTE defined immediately after Request.  Now take note of the difference in declaration between Request and Latest.  In Latest, I define the column names up front and have the columns aliased differently inside the CTE.  I do not define the column_alias list for the Request derived table.  You can also note that the colum_alias defined prior to the guts of the Latest derived table take precedence over any column_alias defined inside that particular derived table.

I hope this was new information to somebody else.  If you learned something new, let me know.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

August 2011
« Jul   Sep »


  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017