SQL ALIASES

SQL ALIASES (temporary names for columns or tables) with syntax and examples. SQL ALIASES can be used to create a temporary name for columns or tables.

Column aliases are used to make it easier to read column headers in a set of results.

Table aliases are used to shorten your SQL code to make it easier to read or when you connect yourself (i.e. enumerate the same table more than once in the FROM statement).

Column alias syntax in SQL

col_name [AS] alias_name_id

Syntax for table alias in SQL

tab_name [AS] alias_name_id

where:

• col_name – the original name of the column to which you want an alias.
• tab_name – the original name of the table you want to give an alias to.
• alias_name_id – an alias for an appointment.

Note:
If alias_name contains spaces, you must quote alias_name_id.

It is acceptable to use spaces in column name alias. However, it is generally not recommended to use spaces when creating table name aliases.

alias_name_id is only valid within an SQL statement.

Column name alias

Typically, aliases are used to make it easier to read column headers in a set of results. Most often you will use a column alias when using a statistical function such as MIN, MAX, AVG, SUM or COUNT in the query.

Let us consider an example of using a column name alias in SQL.

In this example, we have a table with the following data:

Let’s show you how to create an alias for a column. Enter the following SQL statement:

SELECT dept1_id, COUNT(*) AS total_id
FROM empls
GROUP BY dept1_id;

Two entries will be selected. Here are the results you will get:

In this example, we used the alias total_id for COUNT(*). The result is that the final value will be displayed as a header for the second column when returning the result set. Since there were no spaces in our alias, we do not need to quote the alias.

Now let us rewrite our query to include a space in the column alias:

SELECT dept1_id, COUNT(*) AS "total_id empls"
FROM empls
GROUP BY dept1_id;

Two entries will be selected. Here are the results you will get:

In this example, we have added the alias “total employees” to the COUNT(*) field, so it will be the header for the second column in our results set. Since there are spaces in this column alias, “total employees” should be enclosed in quotes in the SQL statement.

An alias for the table name

When you create a table alias, this is because you plan to list the same table name more than once in FROM, 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 a table name alias in SQL. In this example, we have a table with the following data:

And a table with the name of the categories with the following data:

Now let us combine these 2 tables and the aliases of each table name. Enter the following SQL statement:

SELECT p.prod_name, c.cat_name
FROM prods AS p
INNER JOIN cats AS c
ON p.cat_id = c.cat_id
WHERE p.prod_name <> 'Pear';

5 records will be selected. Here are the results you will get:

In this example, we created an alias for the products table and an alias for the category table. In this SQL statement, we can now reference the products table as p and the category table as c.

When creating table aliases, there is no need to create aliases for all tables listed in the FROM sentence. You can create aliases for any or all tables.

How to use Aliases in SQL