Posted by : Glen Sajori
Sabtu, 27 September 2014
Description
SQL ALIASES can be used to create a temporary name for columns or tables.- COLUMN ALIASES are used to make column headings in your result set easier to read.
- TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).
Syntax
The syntax to ALIAS A COLUMN in SQL is:column_name AS alias_name
OR
The syntax to ALIAS A TABLE in SQL is:
table_name alias_name
Parameters or Arguments
- column_name is the original name of the column that you wish to alias.
- table_name is the original name of the table that you wish to alias.
- alias_name is the temporary name to assign.
Note
- If the alias_name contains spaces, you must enclose the alias_name in quotes.
- It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
- The alias_name is only valid within the scope of the SQL statement.
Example - ALIAS a column
Generally, aliases are used to make the column headings in your
result set easier to read. For example, when using the COUNT function,
you might alias the result of the COUNT function.
For example:
SELECT department, COUNT(*) AS TOTAL FROM employees GROUP BY department;
In this example, we've aliased the COUNT(*) field as TOTAL. As a
result, TOTAL will display as the heading for the second column when
the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes. However, it would have been perfectly acceptable to write this example using quotes as follows:
SELECT department, COUNT(*) AS "TOTAL" FROM employees GROUP BY department;
Next, let's look at an example where we are required to enclose the alias_name in quotes.
For example:
SELECT department, COUNT(*) AS "TOTAL EMPLOYEES" FROM employees GROUP BY department;
In this example, we've aliased the COUNT(*) field as "TOTAL EMPLOYEES". Since there are spaces in this alias_name, "TOTAL EMPLOYEES" must be enclosed in quotes.
Example - ALIAS a Table
When you create an alias on a table, it is either because you
plan to list the same table name more than once in the FROM clause (ie:
self join), or you want to shorten the table name to make the SQL
statement shorter and easier to read. Let's look at an example of how to alias a table name in SQL.
For example:
SELECT s.supplier_id, s.supplier_name, order_details.order_date FROM suppliers s INNER JOIN order_details ON s.supplier_id = order_details.supplier_id WHERE s.supplier_id > 5000;
In this example, we've created an alias for the suppliers table called s. Now within this SQL statement, we can refer to the suppliers table as s. When creating table aliases, it is not necessary to create
aliases for all of the tables listed in the FROM clause. You can choose
to create aliases on any or all of the tables.
SELECT s.supplier_id, s.supplier_name, od.order_date FROM suppliers s INNER JOIN order_details od ON s.supplier_id = od.supplier_id WHERE s.supplier_id > 5000;
Now we have an alias for order_details table called od as well as the alias for the suppliers table called s.
Source : techonthenet.com