MySQL - Aliases

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 −

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 −

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 −

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 −

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 −

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 −

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 −