SQL 4 begginers - Alias

In most online articles about databases you can easily find a lot of articles explaining cool, complex and new features. But what’s about some basics? I decided to release some articles about the basics. The articles will mostly cover topics usable in most relational databases, but the examples and the focus will be on MS SQL Server database engine.

Alias is a shorten name for a table (sometime even a function) that can be used in queries.

When joining one or more tables (or using tables in sub selects) there is a big possibility that we are dealing with very large table names (e.g. [Customer_Invoices].[Customer_Invoice_Header_Archive] and [Customer_Invoices].[Customer_Invoice_Detail_Archive]).

Imagine the situation that you must join the two tables, based on the the column of the same name existing in both tables:

SELECT
	Invoice_Primary_key
	, Column_Name_1
	, Column_Name_2
	, ...
FROM
	[Customer_Invoices].[Customer_Invoice_Header_Archive]
	INNER JOIN [Customer_Invoices].[Customer_Invoice_Detail_Archive] ON Invoice_Primary_key = Invoice_Primary_key
WHERE
	...

There are multiple problems in this query.

  1. It is not possible to join tables because the SQL engine don't know from what table is the first Invoice_Primary_Key and from what table is the second one in the ON part of this statement.
  2. The second problem is that in the SELECT part of the query we want to display the same column (Invoice_Primary_Key), but the SQL engine don't know if he should take it from the first table or the second and we will get the exception:

"Ambiguous column name Invoice_Primary_Key"

To solve the first problem, you must put the table names before each column in the ON part of the statement:

SELECT
	Invoice_Primary_key
	, Column_Name_1
	, Column_Name_2
	, ...
FROM
	[Customer_Invoices].[Customer_Invoice_Header_Archive]
	INNER JOIN [Customer_Invoices].[Customer_Invoice_Detail_Archive] ON [Customer_Invoices].[Customer_Invoice_Header_Archive].Invoice_Primary_key = [Customer_Invoices].[Customer_Invoice_Detail_Archive].Invoice_Primary_key
WHERE
	...

To solve the second problem, you must put the table name before the column name:

SELECT
	[Customer_Invoices].[Customer_Invoice_Header_Archive].Invoice_Primary_key
	, Column_Name_1
	, Column_Name_2
	, ...
FROM
	[Customer_Invoices].[Customer_Invoice_Header_Archive]
	INNER JOIN [Customer_Invoices].[Customer_Invoice_Detail_Archive] ON [Customer_Invoices].[Customer_Invoice_Header_Archive].Invoice_Primary_key = [Customer_Invoices].[Customer_Invoice_Detail_Archive].Invoice_Primary_key
WHERE
	...

Oh, this is completely nice! Image that you must put the table name before each column name :)

So, what can we do to solve our problems? We can use aliases. Aliases are usually shortening names of tables (or table functions). To specify an ALIAS we put the AS statement after the object name followed by the shorten name. You can also alias on an object with no usage of AS statement:

... FROM [Customer_Invoices].[Customer_Invoice_Header_Archive] AS Header

or

FROM [Customer_Invoices].[Customer_Invoice_Header_Archive] Header

Let's see our imaginary query now:

SELECT
	Header.Invoice_Primary_key
	, Header.Column_Name_1
	, Detail.Column_Name_2
	, ...
FROM
	[Customer_Invoices].[Customer_Invoice_Header_Archive] AS Header
	INNER JOIN [Customer_Invoices].[Customer_Invoice_Detail_Archive] AS Detail ON Header.Invoice_Primary_key = Detail.Invoice_Primary_key
WHERE
	...

We could use only "H" and "D" instead of "Header" and "Detail", but you must admit that the query is now much readable.

And to conclude i will show you an example that will show you why you should ALWAYS put the table name or alias before the columns in select statements. Let's create two temporary tables with different column names and select something from the first table using the second table in the IN statement.

CREATE TABLE #Table1 (
	Column1 INT
	, Column2 INT
)

CREATE TABLE #Table2 (
	Column3 INT
	, Column4 INT
)

SELECT Column1, Column2 FROM #Table1 WHERE Column1 IN (SELECT Column2 FROM #Table2)

I just run this query and i didn't get any error. What's strange? If you take a deeper look you will see that i am selecting Column2 from the Table2 and that the Table2 does not contain column of that name!!! SQL engine made a mistake since the Column2 exists in the first table. Try to insert some real values in Table1 and Table2 and you will see that the result set is completely wrong!!!

But if we use the table names (or aliases) in every column name the execution of this query will be impossible:

SELECT T1.Column1, T1.Column2 FROM #Table1 T1 WHERE Column1 IN (SELECT T2.Column2 FROM #Table2 T2)

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (695.00 bytes)