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