The World of MySQL & MariaDB

Complete MySQL AS Guide: Alias Columns and Tables

Operators and Clauses
目次

1. Introduction

The MySQL AS keyword is used to assign aliases to tables and columns within a query. This feature not only improves the readability of SQL statements but also helps express complex queries concisely. In this article, we will thoroughly explain everything from the basic usage of AS to real-world examples and cautions. In particular, we will provide explanations with concrete code examples to make it easy for beginners to understand.

2. Basic Usage of the “AS” Keyword

2.1. Assigning Aliases to Columns (Fields)

In MySQL, you can assign aliases to columns to use more understandable column names. The basic syntax is as follows.
SELECT column_name AS alias_name FROM table_name;

Example

For example, when concatenating the first_name and last_name columns of the users table and assigning the alias full_name:
SELECT first_name, last_name, CONCAT(first_name, ' ', last_name) AS full_name FROM users;
Executing this SQL creates a column named full_name, displaying the concatenated result of first_name and last_name.

2.2. Assigning Aliases to Tables

You can also assign aliases to tables. This allows you to shorten long table names and improves the readability of complex queries. The basic syntax is as follows.
SELECT t1.column_name FROM table_name AS t1;

Example

For example, when assigning the alias e to the employees table:
SELECT e.id, e.name FROM employees AS e;
Using this method, you can write SQL statements concisely by using e instead of employees.

3. Why Use the “AS” Keyword

3.1. Improving Query Readability

Using AS makes the query more intuitively understandable. It is especially useful when column names are long or when treating calculation results as columns.

3.2. Avoiding Ambiguity When Joining Multiple Tables

When using JOIN, ambiguity arises if the same column name exists in multiple tables. In such cases, you can avoid it by assigning aliases to the tables.
SELECT e.name, d.department_name FROM employees AS e JOIN departments AS d ON e.department_id = d.id;
In this query, the employees table uses the alias e, and the departments table uses the alias d, making it more readable.

3.3. Assigning Aliases to Calculated Columns and Function Results

By assigning aliases to SQL calculation results or function results, you can obtain clearer output.
SELECT price * quantity AS total_cost FROM orders;
In this query, the result of price * quantity is given the clear name total_cost.

4. Specific Use Cases

4.1. Using AS in Multi-Table Joins

When joining different tables, assigning aliases to tables can shorten the query.
SELECT u.id, u.name, o.order_date 
FROM users AS u 
JOIN orders AS o ON u.id = o.user_id;
In this example, the users table is defined as u and the orders table as o, eliminating redundant notation.

4.2. Assigning Aliases to Calculation Results

When calculating total amounts, giving the result an alias makes the display more understandable.
SELECT product_name, price * quantity AS total_price FROM sales;
As a result, the total_price column contains the calculation result of price * quantity.

5. Notes

5.1. “AS” can be omitted, but explicit usage is recommended

In MySQL, you can omit AS, but it is recommended to write it explicitly to improve code readability.
SELECT first_name full_name FROM users;  -- optional
SELECT first_name AS full_name FROM users;  -- recommended

5.2. Caution when using reserved words as aliases

When using an SQL reserved word as an alias, you need to enclose it in backticks (`).
SELECT column_name AS `key` FROM table_name;

6. FAQ (Frequently Asked Questions)

Q1: Is it okay to omit “AS”?

A1: It can be omitted, but we recommend specifying it explicitly for better readability.

Q2: Is it okay to give the same alias to both a table and a column?

A2: To avoid confusion, it’s advisable to use different aliases for tables and columns.

Q3: Can an alias contain spaces?

A3: Yes, but you need to enclose it in double quotes "" or backticks ``.
SELECT column_name AS "My Custom Name" FROM table_name;

Q4: Can you create a temporary table using “AS”?

A4: “AS” is not used for creating temporary tables. To create a temporary table, use CREATE TEMPORARY TABLE.
CREATE TEMPORARY TABLE temp_table AS SELECT * FROM original_table;

Q5: Can you create a view using “AS”?

A5: When creating a view, you can define it using “AS”.
CREATE VIEW sales_summary AS 
SELECT product_name, SUM(price * quantity) AS total_sales FROM sales GROUP BY product_name;

7. Summary

In this article, we explained the MySQL “AS” keyword in detail. To summarize the key points, they are as follows.
  • Using “AS” allows you to assign clear aliases to columns and tables.
  • It improves query readability and makes SQL easier to understand.
  • When using a reserved word as an alias, you need to enclose it in backticks.
When mastering MySQL, “AS” is a very handy feature. Use it appropriately to write efficient SQL queries!