Create a temporary name using ALIAS in SQL

SQL ALIAS is used to create a temporary name (called an alias) for a column or table. This command allows you to change the temporary name for a table or column with a different name in the database.

• TABLE ALIAS is used to shorten SQL for easier readability or when you need to manually connect (for example, listing the same table more than once in the FROM clause).
• COLUMN ALIAS is used to create column headers in the results for easy viewing or meeting the purpose of a particular SQL query.

Changing this name is temporary and does not change the original table name in the database.

Syntax

The syntax of TABLE ALIAS (table alias) is as follows:

 SELECT cot1, cot2. 
 FROM ten_bang AS ten_tamthoi 
 WHERE [dieu_kien]; 

The syntax of COLUMN ALIAS (alias column) is as follows:

 SELECT ten_cot AS ten_tamthoi 
 FROM ten_bang 
 WHERE [dieu_kien]; 

Example of using ALIAS

Suppose the two tables are NHANVIEN and TIENTHUONG with the following records:

Table 1: NHANVIEN

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

Table 2: TIENTHUONG

 +-----+---------------------+-------------+--------+ |TT_ID| NGAY | NHANVIEN_ID | SOTIEN | +-----+---------------------+-------------+--------+ | 102 | 2019-01-08 00:00:00 | 3 | 3000 | | 100 | 2019-01-08 00:00:00 | 3 | 1500 | | 101 | 2019-02-20 00:00:00 | 2 | 1560 | | 103 | 2018-12-20 00:00:00 | 4 | 2060 | +-----+---------------------+-------------+--------+ 

Here's how to use TABLE ALIAS in SQL:

 SQL> SELECT C.ID, C.TEN, C.TUOI, O.SOTIEN 
  FROM NHANVIEN AS C, TIENTHUONG AS O 
  WHERE C.ID = O.NHANVIEN_ID; 

The result is:

+----+----------+-----+--------+ | ID | NAME | AGE | AMOUNT | +----+----------+-----+--------+ | 3 | Nga | 23 | 3000 | | 3 | Nga | 23 | 1500 | | 2 | Loan | 25 | 1560 | | 4 | Manh | 25 | 2060 | +----+----------+-----+--------+ 

And the usage of COLUMN ALIAS in SQL is as follows:

 SQL> SELECT ID AS NHANVIEN_ID, TEN AS NHANVIEN_TEN 
  FROM NHANVIEN 
  WHERE LUONG IS NOT NULL; 

The above example will return the result:

+-------------+---------------+ | NHANVIEN_ID | NHANVIEN_NAME | +-------------+---------------+ | 1 | Thanh | | 2 | Loan | | 3 | Nga | | 4 | Manh | | 5 | Huy | | 6 | Cao | | 7 | Lam | +-------------+---------------+ 

In the next articles, Quantrimang will discuss with you the index (INDEX) in SQL. Have you remembered to it!

Previous article: NULL value in SQL

Next article: Index (INDEX) in SQL