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.

1. 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).
2. 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