MS SQL Server: ALIAS in SQL Server

ALIASES in SQL Server helps create temporary names (called aliases) for columns or tables.

• COLUMN ALIASES helps create column headers in results for easy viewing
• TABLE ALIASES helps shorten SQL for easier readability or when you need to manually connect (As an example,, listing the same table more than once in the FROM clause).

Syntax for ALIASES alias

Syntax to set alias for column

 ten _cot [ AS ] bi_danh 

or set the alias for the table

 ten_bang [AS] bi_danh 

Variable name or variable value

ten_cot

The original name of the column to which you want the alias

ten_bang

The original name of the table to which you want the alias

AS

option. Most programmers use the keyword AS when setting the alias for the column but not when setting the table. Whether it is used or not, it does not affect the alias in MySQL. Unlike other databases, this is an optional option in MySQL. (The examples below will use AS when setting aliases for columns and removing AS when setting tables).

bi_danh

temporary names set for columns or tables.

Note

• If bi_danh contains spaces, it must be placed in quotation marks.
• You can use spaces when setting aliases for columns. But often, do not use spaces when setting tables.
• The bi-name is only valid in SQL statements.

As an example, - set the alias for the column

Often aliases help create column headers in results that are easy to see.

 SELECT  nhanvien_id, ten + ho AS NAME 
 FROM nhanvien 
 WHERE ten = 'Sarah'; 

In this example, we set the alias for the second column (ie, combining the first and last name) as NAME. In the result, NAME will be the title of the second column. Because the above bi_danh has no spaces, no quotes are needed. But if you want, use this mark.

 SELECT nh anvien_id, ten + ho AS 'NAME' 
 FROM nhanvien 
 WHERE ten = 'S arah'; 

This is another example that needs to put bi_danh in quotation marks.

 SELECT nhanvie n_id, ten + ho AS 'TEN NHAN VIEN' 
 FROM nhanvien 
 WHERE ten = 'Sara h'; 

In this example, the second column in the result is set to TEN NHAN VIEN.

As an example, - set the alias for the table

Setting the alias for the table can help list a table more than once in the FROM clause (or self-connect) or shorten the table name to make it easier to read.

 SELECT s.sanp ham_ten, hangtonkho.chatluong 
 FROM sanpham s 
 INNER JOIN hangtonkho 
 ON s.sanpham_id = hangtonkho.sanpham_id 
 ORDER BY s.tsanph am_ten ASC, hangtonkho.chatluong DESC; 

The above example creates an alias for the sanpham table which is s. Now in the SQL statement, it is possible to call the sanpham table s. When creating a table alias, it is not necessary to create an alias for all tables given in the FROM clause. As an example,, create an alias for the hangtonkho table as shown below.

 SELECT s.sanpham_ten, h.ch atluong 
 FROM sanpham s 
 INNER JOIN hangtonkho h 
 ON s.sanpham_id = h.sanpham_id 
 ORDER BY s.sanpham_ten ASC, h.chatluong DESC; 

Next lesson: JOIN in SQL Server