MySQL - Alias

MySQL - Using Aliases

In SQL, an alias is a temporary, alternative name given to a table or a column in a query. Aliases are used to make column names more readable and to simplify queries, especially when working with JOIN operations or complex expressions.

The MySQL Alias

Aliases are assigned using the AS keyword. While the AS keyword is technically optional in MySQL, using it is a strong best practice as it makes the query’s intent much clearer.

Aliasing Column Names

Column aliases are frequently used to provide more descriptive names for the columns in your result set or to name a column derived from an expression or aggregate function.

Syntax

SELECT column_name AS alias_name
FROM table_name;

-- Or with an expression
SELECT expression AS alias_name
FROM table_name;

Example Setup

Let’s use the same customers table from previous examples.

SELECT * FROM customers LIMIT 3;

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

Example 1: Simple Column Aliases

In this query, we rename the id and name columns in the output.

SELECT id AS customer_id, name AS customer_name
FROM customers;

The result set will have headers customer_id and customer_name.

Example 2: Aliasing Aggregate Functions

A very common use case is to name the result of an aggregate function like COUNT(), SUM(), or AVG().

SELECT COUNT(id) AS total_customers, AVG(salary) AS average_salary
FROM customers;

This produces a clean, readable result:

total_customers	average_salary
7	5000.00

Example 3: Aliasing Concatenated Columns

You can create a new virtual column from existing data and give it an alias.

SELECT id, CONCAT(name, ' from ', address) AS location_info
FROM customers;

id	location_info
1	Ramesh from Ahmedabad
2	Khilan from Delhi
…and so on	…

Aliasing Table Names

Table aliases are essential for writing concise and readable JOIN queries. They are mandatory when performing a self-join.

Syntax (Modern JOIN Syntax)

The modern, explicit JOIN syntax is strongly preferred over the old comma-based syntax because it clearly separates join conditions from filtering conditions (WHERE clause).

SELECT t1.column, t2.column
FROM table1 AS t1
JOIN table2 AS t2 ON t1.common_column = t2.common_column;

Example: Joining customers and orders

Let’s create an orders table and join it with customers to find out who ordered what.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO orders VALUES
(101, 3, '2023-10-08', 3000.00),
(102, 2, '2023-11-20', 1560.00),
(103, 3, '2023-10-08', 1500.00),
(104, 4, '2023-05-20', 2060.00);

Now, let’s join them using short, clear aliases (c for customers, o for orders).

SELECT c.name, c.address, o.order_date, o.amount
FROM customers AS c
JOIN orders AS o ON c.id = o.customer_id
ORDER BY c.name;

This would produce the following result:

name	address	order_date	amount
Chaitali	Mumbai	2023-05-20	2060.00
Kaushik	Kota	2023-10-08	3000.00
Kaushik	Kota	2023-10-08	1500.00
Khilan	Delhi	2023-11-20	1560.00

Aliasing with a Self-Join

A self-join is when you join a table to itself. In this case, table aliases are not just helpful—they are required to distinguish between the two instances of the same table.

Example: Finding Employees and Their Managers

A classic example is an employees table where a manager_id column refers back to another employee’s id in the same table. We can use a self-join to list each employee next to their manager’s name.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', NULL), -- The CEO has no manager
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

Here, we join employees to itself, aliasing one instance as e (for employee) and the other as m (for manager). A LEFT JOIN is used to include the CEO who has no manager.

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM
    employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id;

Output of the above query is as follows:

employee_name	manager_name
Alice
Bob	Alice
Charlie	Alice
David	Bob