How To Create Table Alias In Sql

Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.

SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

7 July 2020

SQL ALIASES

S QL 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:

empl_number	f_name	l_name	salary_id	dept1_id
1001	Justin	Bieber	62000	500
1002	Selena	Gomez	57500	500
1003	Mila	Kunis	71000	501
1004	Tom	Cruise	42000	501

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:

dept1_id	total_id
500	2
501	2

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:

dept1_id	total_id empls
500	2
501	2

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:

prod_id	prod_name	cat_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

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

cat_id	cat_name
25	Deli
50	Produce
75	Bakery
100	General Merchandise
125	Technology

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:

prod_name	cat_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli

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

Tags: SQL, sql server, sqls, sqls ms, T-SQL, TSQL