{"id":211,"date":"2018-04-23T21:05:41","date_gmt":"2018-04-23T14:05:41","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=211"},"modified":"2024-03-02T16:01:40","modified_gmt":"2024-03-02T09:01:40","slug":"sql-server-alias","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-alias\/","title":{"rendered":"SQL Server Alias"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server aliases, including column aliases and table aliases.<\/p>\n\n\n\n

SQL Server column alias #<\/a><\/h2>\n\n\n\n

When you use the SELECT<\/a><\/code> statement to query data from a table, SQL Server uses the column names as the column headings for the output. For example:<\/p>\n\n\n

SELECT<\/span>\n    first_name,\n    last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The output indicates that the SQL Server uses the first_name<\/code> and last_name<\/code> column names as the column headings respectively.<\/p>\n\n\n\n
To obtain the full names of customers, you can concatenate the first name, space, and the last name using the concatenation\u00a0\u00a0+<\/code> operator as shown in the following query:<\/p>\n\n\n
SELECT<\/span>\n    first_name + ' '<\/span> + last_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
SQL Server returns the full name column as ( No column name<\/code>) which is not meaningful in this case.<\/p>\n\n\n\n
To assign a column or an expression a temporary name during the query execution, you use a column alias.<\/p>\n\n\n\n
The following illustrates the column alias syntax:<\/p>\n\n\n
column_name | expression  AS column_alias<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you use the AS<\/code> keyword to separate the column name or expression and the alias.<\/p>\n\n\n\n
Because the AS<\/code> keyword is optional, you can assign an alias to a column as follows:<\/p>\n\n\n
column_name | expression column_alias<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Back to the example above, you can rewrite the query using a column alias:<\/p>\n\n\n
SELECT<\/span>\n    first_name + ' '<\/span> + last_name AS<\/span> full_name\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that if the column alias contains spaces, you need to enclose it in quotation marks, as shown in the following example:<\/p>\n\n\n
SELECT<\/span>\n    first_name + ' '<\/span> + last_name AS<\/span> 'Full Name'<\/span>\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
The following example shows how to assign an alias to a column:<\/p>\n\n\n
SELECT<\/span>\n    category_name 'Product Category'<\/span>\nFROM<\/span>\n    production.categories;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, the product category column alias is much more clear than the category_name<\/code> column name.<\/p>\n\n\n\n
When you assign a column an alias, you can use either the column name or the column alias in the ORDER BY<\/a><\/code> clause as shown in the following example:<\/p>\n\n\n
SELECT<\/span>\n    category_name 'Product Category'<\/span>\nFROM<\/span>\n    production.categories\nORDER<\/span> BY<\/span>\n    category_name;  \n\n\nSELECT<\/span>\n    category_name 'Product Category'<\/span>\nFROM<\/span>\n    production.categories\nORDER<\/span> BY<\/span>\n    'Product Category'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that SQL Server processes the ORDER BY<\/code> clause after the SELECT<\/code> clause, so you can use column aliases in the ORDER BY<\/code> clause.<\/p>\n\n\n\n
SQL Server table alias #<\/a><\/h2>\n\n\n\n
A table can be given an alias, which is known as a correlation name or range variable<\/a>.<\/p>\n\n\n\n
Similar to the column alias, you can assign a table a temporary name with or without the AS<\/code> keyword:<\/p>\n\n\n
table_name AS table_alias\ntable_name table_alias\n<\/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    sales.customers.customer_id,\n    first_name,\n    last_name,\n    order_id\nFROM<\/span>\n    sales.customers\nINNER<\/span> JOIN<\/span> sales.orders ON<\/span> sales.orders.customer_id = sales.customers.customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, both the customers<\/code> and the orders<\/code> tables have a\u00a0column with the same name customer_id<\/code>, so you need to refer to the column using the following syntax:<\/p>\n\n\n
table_name.column_name<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
such as:<\/p>\n\n\n
sales.custoners.customer_id\nsales.orders.customer_id\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t do so, the SQL server will issue an error.<\/p>\n\n\n\n
The query above is quite difficult to read. Fortunately, you can improve its readability by using the table alias as follows:<\/p>\n\n\n
SELECT<\/span>\n    c.customer_id,\n    first_name,\n    last_name,\n    order_id\nFROM<\/span>\n    sales.customers c\nINNER<\/span> JOIN<\/span> sales.orders o ON<\/span> o.customer_id = c.customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this query, c<\/code> is the alias for the sales.customers<\/code> table and o<\/code> is the alias for the sales.orders<\/code> table.<\/p>\n\n\n\n
When you assign an alias to a table, you must use the alias to refer to the table column. Otherwise, SQL Server will issue an error.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • A column alias is a temporary name assigned to a column or an expression in a query’s result set.<\/li>\n\n\n\n
  • Use a column alias to rename the output of a column or an expression to make it more meaningful.<\/li>\n\n\n\n
  • A table alias is a shorthand or temporary name assigned to a table in a query.<\/li>\n\n\n\n
  • Use table aliases when joining multiple tables or when referencing the same table more than once 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