Khóa học miễn phí MySQL – Alias nhận dự án làm có lương

MySQL – Aliases

Table of content


The MySQL Alias

The MySQL Alias is used to assign a temporary name, called an Alias to a table or a column in SQL.

Aliases are created using the AS keyword and are used to refer to a specific table or a column without changing its original name. They are used to make the query easily readable when working tables or columns with similar names.

Aliasing Column Names

Aliasing column names is used to assign a different name to a column of a table.

Syntax

The basic syntax of a column alias is as follows −

SELECT column_name 
AS alias_name
FROM table_name;

Example

First, let us create a table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, let us insert values into the table created above using the INSERT INTO statement as shown below −

INSERT INTO CUSTOMERS VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table obtained is as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Example

In the following query, we are creating two aliases, one for the ID column and one for the AGE column −

SELECT ID AS CUST_ID, AGE 
AS CUST_AGE
FROM CUSTOMERS;

Output

The output of the above query is produced as given below −

CUST_ID CUST_AGE
1 32
2 25
3 23
4 25
5 27
6 22
7 24

Example

If we want the alias name to contain spaces, we can use the double quotation marks as shown in the query below −

SELECT ID AS "CUST ID", AGE 
AS "CUST AGE"
FROM CUSTOMERS;

Output

On executing the given query, the output is displayed as follows −

CUST ID CUST AGE
1 32
2 25
3 23
4 25
5 27
6 22
7 24

Example

In the query below, we are creating an alias named ”INFORMATION” that combines two columns (AGE, ADDRESS) −

SELECT ID, CONCAT(AGE, '', '', ADDRESS) 
AS INFORMATION
FROM CUSTOMERS;

Output

When we execute the above query, the output is obtained as follows −

ID INFORMATION
1 32, Ahmedabad
2 25, Delhi
3 23, Kota
4 25, Mumbai
5 27, Bhopal
6 22, Hyderabad
7 24, Indore

Aliasing Table Names

Aliasing table names is used to assign a different name to a table.

Syntax

Following is the syntax of a table alias −

SELECT column1, column2....
FROM table_name AS alias_name

Example

Let us create another table with the name ORDERS using the following query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATES DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,
   PRIMARY KEY (OID)
 );

Now, let us insert values into the table created above using the INSERT INTO statement as follows −

INSERT INTO ORDERS VALUES 
(102, ''2009-10-08 00:00:00'', 3, 3000),
(100, ''2009-10-08 00:00:00'', 3, 1500),
(101, ''2009-11-20 00:00:00'', 2, 1560),
(103, ''2008-05-20 00:00:00'', 4, 2060);

The ORDERS table obtained is as follows −

OID DATES CUSTOMER_ID AMOUNT
100 2009-10-08 00:00:00 3 1500
101 2009-11-20 00:00:00 2 1560
102 2009-10-08 00:00:00 3 3000
103 2008-05-20 00:00:00 4 2060

In the following query, the CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” −

SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE  C.ID = O.CUSTOMER_ID;

Output

This would produce the following result −

ID NAME AGE AMOUNT
3 Kaushik 23 1500
2 Khilan 25 1560
3 Kaushik 23 3000
4 Chaitali 25 2060

Aliasing with Self Join

The MySQL Self Join is used to join a table to itself as if it were two separate tables. Aliasing in self join is used to temporarily rename the table in the SQL statement to prevent confusion.

Syntax

Following is the syntax for performing a self-join with aliases −

SELECT column_name(s)
FROM my_table a, my_table b
ON a.join_column = b.join_column

Example

Now, let us join the CUSTOMERS table to itself using the self join to establish a relationship among the customers on the basis of their earnings.

Here, we are aliasing column names and table names to create a more meaningful resultant table.

SELECT a.ID, b.NAME 
AS EARNS_HIGHER, a.NAME 
AS EARNS_LESS, a.SALARY 
AS LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

Output

Output of the above query is as follows −

ID EARNS_HIGHER EARNS_LESS LOWER_SALARY
2 Ramesh Khilan 1500.00
2 Kaushik Khilan 1500.00
6 Chaitali Komal 4500.00
3 Chaitali Kaushik 2000.00
2 Chaitali Khilan 1500.00
1 Chaitali Ramesh 2000.00
6 Hardik Komal 4500.00
4 Hardik Chaitali 6500.00
3 Hardik Kaushik 2000.00
2 Hardik Khilan 1500.00
1 Hardik Ramesh 2000.00
3 Komal Kaushik 2000.00
2 Komal Khilan 1500.00
1 Komal Ramesh 2000.00
6 Muffy Komal 4500.00
5 Muffy Hardik 8500.00
4 Muffy Chaitali 6500.00
3 Muffy Kaushik 2000.00
2 Muffy Khilan 1500.00
1 Muffy Ramesh 2000.00

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc