When reading data from tables, we may need to give different names to tables and columns. We can do this by defining alias to columns or tables.
In our first example we will give different names to the tables.
Example1:
We usually need to give Alias when we join the tables.
First, let’s create two tables as below and add a few records to these tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [TestDB] GO CREATE TABLE [dbo].[MyTable_1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MyTable_2]( [ID] [int] IDENTITY(1,1) NOT NULL, [SurName] [varchar](50) NULL, ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTableTarget] VALUES ('Nurullah'),('Faruk') INSERT INTO [dbo].[MyTableSource] VALUES ('CAKIR'),('ERDEM') |
Then try to run the following join query.
|
1 2 |
SELECT [ID],[Name],[Surname] FROM [dbo].[MyTable_1] INNER JOIN [dbo].[MyTable_2] ON [dbo].[MyTable_1].[ID]=[dbo].[MyTable_2].[ID] |
When we run the query we will get the error as follows.
Msg 209, Level 16, State 1, Line 17
Ambiguous column name ‘ID’.
The reason for the error is that the ID column exists in both tables.
In this case, the problem will be solved when we write the query using alias. We can use alias as follows.
|
1 2 |
SELECT tbl1.[ID],tbl1.[Name],tbl2.[Surname] FROM [dbo].[MyTable_1] tbl1 INNER JOIN [dbo].[MyTable_2] tbl2 ON tbl1.[ID]=tbl2.[ID] |
In the second example we will describe the alias to the columns.
Example2:
When joining the first table and the second table, combine the name and surname with the + operator. As a result, let’s give the column a different name with an alias.
|
1 2 |
SELECT tbl1.[ID],tbl1.[Name] +' '+tbl2.[Surname] AS 'Name_Surname' FROM [dbo].[MyTable_1] tbl1 INNER JOIN [dbo].[MyTable_2] tbl2 ON tbl1.[ID]=tbl2.[ID] |
