{"id":2642,"date":"2013-05-21T17:01:56","date_gmt":"2013-05-22T01:01:56","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2642"},"modified":"2023-12-28T22:17:31","modified_gmt":"2023-12-29T05:17:31","slug":"mysql-alias","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-alias\/","title":{"rendered":"MySQL Aliases"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL aliases<\/strong> to assign temporary names to columns or tables in a query.<\/p>\n\n\n\n

MySQL supports two kinds of aliases: column aliases and table aliases.<\/p>\n\n\n\n

Column aliases<\/h2>\n\n\n\n

In MySQL, you use column aliases to assign a temporary name to a column in the query’s result set.<\/p>\n\n\n\n

For example, column names sometimes are so technical that make the query’s output very difficult to understand. To give a column a descriptive name, you can use a column alias.<\/p>\n\n\n\n

The following statement illustrates how to use the column alias:<\/p>\n\n\n

SELECT<\/span> \n   [column_1 | expression] AS<\/span> descriptive_name\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To assign a column an alias, you use the AS<\/code> keyword followed by the alias. If the alias contains spaces, you must enclose it in quotes as follows:<\/p>\n\n\n
SELECT<\/span> \n   [column_1 | expression] AS<\/span> `descriptive name`<\/span>\nFROM<\/span> \n   table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because the AS<\/code> keyword is optional, you can omit it in the statement. Note that you can also assign an expression an alias.<\/p>\n\n\n\n
Let’s look at the employees<\/code> table in the sample database.<\/a><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following query selects the first names and last names of employees. It uses the CONCAT_WS()<\/a><\/code> function to concatenate first name and last name into full name:<\/p>\n\n\n
SELECT<\/span> \n    CONCAT_WS<\/span>(', '<\/span>, lastName, firstname)\nFROM<\/span>\n    employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The column heading can be challenging to read. To address this issue, you can assign a column alias to the output column, as shown in the following query:<\/p>\n\n\n
SELECT<\/span>\n   CONCAT_WS<\/span>(', '<\/span>, lastName, firstname) AS<\/span> `Full name`<\/span>\nFROM<\/span>\n   employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In MySQL, you can use the column alias in the ORDER BY<\/a><\/code>, GROUP BY<\/a><\/code> and HAVING<\/a><\/code> clauses to reference the column.<\/p>\n\n\n\n
The following query uses the column alias in the ORDER BY<\/code> clause to alphabetically sort the employee’s full names:<\/p>\n\n\n
SELECT<\/span>\n\tCONCAT_WS<\/span>(', '<\/span>, lastName, firstname) `Full name`<\/span>\nFROM<\/span>\n\temployees\nORDER<\/span> BY<\/span>\n\t`Full name`<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The following statement selects orders whose total amount is greater than 60000. It uses column aliases in GROUP BY<\/code> and HAVING<\/code> clauses.<\/p>\n\n\n
SELECT<\/span>\n\torderNumber `Order no.`<\/span>,\n\tSUM<\/span>(priceEach * quantityOrdered) Total\nFROM<\/span>\n\torderdetails\nGROUP<\/span> BY<\/span>\n\t`Order no.`<\/span>\nHAVING<\/span>\n\ttotal > 60000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Notice that you cannot use a column alias in the WHERE<\/a><\/code> clause. The reason is that when MySQL evaluates the WHERE<\/code> clause, the values of columns specified in the SELECT clause have not been evaluated yet.<\/p>\n\n\n\n
Table aliases<\/h2>\n\n\n\n
Similar to column aliases, you can assign a temporary name to a table in a query using a table alias.<\/p>\n\n\n\n
Here’s the basic syntax of table alias:<\/p>\n\n\n
table_name AS table_alias<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the AS<\/code> keyword is optional, so you can choose to omit it.<\/p>\n\n\n\n
This query shows how to assign an alias e to the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> employees e;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Once you assign an alias to a table, you can reference a table column using the table alias like this:<\/p>\n\n\n
table_alias.column_name<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example:<\/p>\n\n\n
SELECT<\/span> \n    e.firstName, \n    e.lastName\nFROM<\/span>\n    employees e\nORDER<\/span> BY<\/span> e.firstName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
See the customers<\/code> and orders<\/code> tables from the sample database<\/a>:<\/p>\n\n\n\n
\"Customers<\/figure>\n\n\n\n
Both tables customers<\/code> and orders<\/code> have the same column name: customerNumber<\/code>. <\/p>\n\n\n\n
If you reference the customerNumber<\/code> column in a query, you will get an error message:<\/p>\n\n\n
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To avoid this error, you use a table alias to qualify the customerNumber<\/code> column:<\/p>\n\n\n
SELECT<\/span>\n\tcustomerName,\n\tCOUNT<\/span>(o.orderNumber) total\nFROM<\/span>\n\tcustomers c\nINNER<\/span> JOIN<\/span> orders o ON<\/span> c.customerNumber = o.customerNumber\nGROUP<\/span> BY<\/span>\n\tcustomerName\nORDER<\/span> BY<\/span>\n\ttotal DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The query above selects the customer name and the number of orders from the customers<\/code> and orders<\/code> tables. <\/p>\n\n\n\n
It uses c<\/code> as a table alias for the customers<\/code> table and o<\/code> as a table alias for the orders<\/code> table. The columns in the customers<\/code> and orders<\/code> tables are referred to via the table aliases.<\/p>\n\n\n\n
If you do not use the alias in the query above, you have to use the table name to refer to its columns, which makes the query more verbose:<\/p>\n\n\n
SELECT<\/span>\n\tcustomers.customerName,\n\tCOUNT<\/span>(orders.orderNumber) total\nFROM<\/span>\n\tcustomers\nINNER<\/span> JOIN<\/span> orders ON<\/span> customers.customerNumber = orders.customerNumber\nGROUP<\/span> BY<\/span>\n\tcustomerName\nORDER<\/span> BY<\/span>\n\ttotal DESC<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use MySQL aliases to assign a column or a table a temporary name.<\/li>\n\n\n\n
  • Use a column alias to assign a temporary name to a column in a query.<\/li>\n\n\n\n
  • Use a table alias to assign a temporary name to a table in a query.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
In practice, you often use table aliases in statements that contain INNER JOIN<\/a><\/code>, LEFT JOIN<\/code><\/a>, RIGHT JOIN<\/a><\/code> clauses or in subqueries<\/a>.<\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n