SQL Aliases

SQL Aliases: Table and Column Aliases Best 3 Example

by

SQL Aliases

In SQL Server development, an alias is a temporary name for a table or column. SQL allows us to interact with databases and perform database operations, such as managing data, using different SQL statements.

SQL ALIASES

One of the most important features in SQL Server is aliases, which can enhance SQL’s readability and flexibility. Aliases can be used for table names or column names to perform different operations using a temporary name, referred to as an SQL alias.

What is an SQL Alias?

In SQL, an alias is a temporary name assigned to a table or column in a query. In SQL Server, an alias is another name for a table or column that can be given while developing different SQL queries.

You can use SQL aliases to make SQL queries more attractive, easier to read, and more understandable. Aliases are often used when working with complex SQL scripts and multiple tables. An SQL alias can be used to give another name to a column or table, allowing you to shorten or modify the table or column name for better clarity. SQL aliases are especially useful when you need to join the same table multiple times, as you can assign instance names to the same table.

Table of Contents

Types of SQL Aliases:

  • Table Alias
  • Column Alias

TABLE ALIAS

Table aliases are optional and are alternative names given to tables within an SQL query. Consider that you have two tables with long names and you want to shorten their names while writing an SQL query, procedure, or function.

Table aliases can be useful when querying with self-joins or working with tables that have long names. In SQL scripts, aliases are used to provide temporary names, which can then be used within the SQL query to join different tables or select column data.

Let’s see an example of a table alias in SQL Server.

Consider that you have two tables, CustomerMasterMaintenance and EmpMaster, and you need to join these tables. Let’s create both tables and insert data into them to write an SQL join query.

Step1. Create Tables.

Create Table CustomerMasterMaintainance
 (
Customer_Number int identity(1,1),
Customer_name varchar(50),
Customer_address varchar(50),
Customer_Contact_number nvarchar(10),
Customer_Occupation varchar(50)
);

Create Script EmpMaster

CREATE TABLE [dbo].[EmpMaster](
	[ID] [int] NOT NULL,
	[EName] [varchar](255) NULL,
	[Email] [varchar](255) MASKED WITH (FUNCTION = 'email()') NULL,
	[Salary] [nvarchar](500) NULL,
	[DOB] [date] NULL,
	[Mobile] [nvarchar](10) MASKED WITH (FUNCTION = 'default()') NULL,
	[Tel] [varchar](10) NULL,
	[Address] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2. Insert Data into Tables

Insert Script for CustomerMasterMaintainance 


Insert into dbo.CustomerMasterMaintainance values('Jagganath Bapurao Bansidhar narve','East NANA Krupa S nivas gopalghare Mumbai 431122','7896541236','Engineer')

Insert into dbo.CustomerMasterMaintainance values('Gopalghare navnath  limbaji','Mhaaralpada dhanori khadakwasla navi mumbai','6547893214','Painter')

Insert into dbo.CustomerMasterMaintainance values('rambhau dhondiba  gorkha','At.Tangdewadi Post sutale Dist Nagpur','8527419369','Farmer')

Insert Script for EmpMaster

How to insert data in SQL Insert 

INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (1, N'Mandar Abhimanyu Matre', N'mandarm@yahoo.com', N'98157', CAST(N'1976-09-04' AS Date), N'8796547854', N'226257', N'sambhaji nagar')
INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (2, N'JOY ROBO', N'joy@yahoo.com', N'4767', CAST(N'1982-08-29' AS Date), N'9595959595', N'244574', N'Mumbai')
INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (3, N'smit yaK', N'smit.yak@rediff.com', N'73335', CAST(N'1992-09-17' AS Date), N'7894561235', N'845664', N'Nagpur')
INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (4, N'MICHEL JACK', N'jAC@BING.com', N'9804', CAST(N'1912-08-02' AS Date), N'6974512365', N'665544', N'Kalkatta')
INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (5, N'TATA SJY', N'tata@rediff.com', N'37572', CAST(N'1865-01-22' AS Date), N'6887412365', N'854644', N'ujjain')
INSERT [dbo].[EmpMaster] ([ID], [EName], [Email], [Salary], [DOB], [Mobile], [Tel], [Address]) VALUES (6, N'Gercy', N'gracy@domain.com', N'47454', CAST(N'1865-01-21' AS Date), N'8885554447', N'2442215544', N'Bihar')

Step3. Write SQL Statement with Table Aliases

Table Alias Example

select cmm.Customer_name,cmm.Customer_occupation,em.Email,em.DOB
from CustomerMasterMaintainance as cmm , EmpMaster as em 
where cmm.Customer_Number=em.ID;

Here, ‘cmm’ is the alias for the ‘CustomerMasterMaintenance’ table and ’em’ is the alias for the ‘EmpMaster’ table. You can use any alias that is easy and understandable to you; in this example, I have used the first letters of the table names. ‘cmm’ stands for Customer Master Maintenance and ’em’ stands for Employee Master.

SQL Aliases Table alias example

COLUMN ALIAS

Column aliases are alternate names given to columns in an SQL query result. Sometimes, when you use functions for a column, the column name might appear as [No column name]. In such cases, you can provide an alias name for the specific columns.

Using alias column names in the output result of an SQL query makes it more understandable. When giving alias names that include spaces, you cannot use spaces directly. Instead, you can use underscores (e.g., Cust_name) or any symbol within words, or you can use spaces enclosed in square brackets (e.g., [Date Of Birth]).

You can assign alias names to columns using ‘AS’ or without ‘AS’.

Column Alias Example
Select Email As Email_ID , 
DOB As [DATE OF BIRT],
Salary Employee_Payment_Amount,
Mobile
from EmpMaster
SQL Aliases Column Alias example

Here, the Email column has been given the alias name ‘Email_Id’, the DOB column has the alias name [DATE OF BIRTH], the Salary column has the alias name Employee_Payment_Amount, and the Mobile column name is used without an alias.

Table and Column Aliases

You can use table and column aliases in a single SQL query. Here is an example: Suppose you have two tables that you need to join. You can join the tables using table alias names and also give alternate names to the columns in the same SQL query.

Example Table and Column Alias
select cmm.Customer_name [Cust Name],
cmm.Customer_occupation Occupation,
em.Email Email_ID
,em.DOB Date_OF_Birth
from CustomerMasterMaintainance as cmm , EmpMaster as em 
where cmm.Customer_Number=em.ID;

Table and column alias example

Benefits of SQL Aliases:



-----ALL ALIASES SQL QUERIES-------


----Table Alias SQL Query-----


select cmm.Customer_name,cmm.Customer_occupation,em.Email,em.DOB
from CustomerMasterMaintainance as cmm , EmpMaster as em 
where cmm.Customer_Number=em.ID

--------COlumn Alias SQL Query------

Select Email As Email_ID , 
DOB As [DATE OF BIRT],
Salary Employee_Payment_Amount,
Mobile
from EmpMaster

-----Table and Column Alias SQL Query----------

select cmm.Customer_name [Cust Name],
cmm.Customer_occupation Occupation,
em.Email Email_ID
,em.DOB Date_OF_Birth
from CustomerMasterMaintainance as cmm
right join  EmpMaster as em 
on  cmm.Customer_Number=em.ID

SQL | Aliases