Basic about ALIASES – using and example

This SQL tutorial explains how to use SQL ALIASES (temporary names for columns or tables) with syntax and examples.

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 simpler to read. TABLE ALIASES are used to shorten your SQL to make it easier to study or when you are performing a self be a part of (ie: listing the identical desk greater than as soon as in the FROM clause).

Subscribe

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 [AS] alias_name

Parameters or Arguments

column_name The original title of the column that you wish to alias. table_name The unique name of the desk that you want to alias. alias_name The brief name to assign.

Note

If the alias_name consists of spaces, you should enclose the alias_name in quotes. It is perfect to use spaces when you are aliasing a column name. However, it is no longer usually desirable practice to use areas when you are aliasing a desk name. The alias_name is only legitimate inside the scope of the SQL statement.

DDL/DML for Examples

If you favor to follow alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your own database!

Get DDL/DML

Example – How to Alias a Column Name

Generally, aliases are used to make the column headings in your end result set simpler to read. Most commonly, you will alias a column when using an combination feature such as MIN, MAX, AVG, SUM or COUNT in your query.

Let’s seem to be at an instance of how to use to alias a column name in SQL.

In this example, we have a desk referred to as personnel with the following data:

employee_number last_name first_name revenue dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Let’s reveal how to alias a column. Enter the following SQL statement:

Try It

SELECT dept_id, COUNT(*) AS total
FROM employees
GROUP BY dept_id;

There will be 2 data selected. These are the effects that you see:

dept_id total

500 2

501 2

In this example, we’ve aliased the COUNT(*) field as total. As a result, complete will display as the heading for the 2nd column when the result set is returned. Because our alias_name did no longer include any spaces, we are not required to enclose the alias_name in quotes.

Now, let’s rewrite our question to include a area in the column alias:

SELECT dept_id, COUNT(*) AS "total employees"
FROM employees
GROUP BY dept_id;

There will be two files selected. These are the consequences that you must see:

dept_id total employees

500 2

501 2

In this example, we’ve aliased the COUNT(*) discipline as “total employees” so this will turn out to be the heading for the 2d column in our end result set. Since there are areas in this column alias, “total employees” should be enclosed in prices in the SQL statement.

Example – How to Alias a Table Name

When you alias a table, it is either due to the fact you plan to list the equal table identify extra than once in the FROM clause (ie: self join), or you desire to shorten the desk name to make the SQL declaration shorter and easier to read.

Let’s seem to be at an example of how to alias a table identify in SQL.

In this example, we have a desk called products with the following data:

product_id product_name category_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 desk referred to as classes with the following data:

category_id category_name 25 Deli 50 Produce 75 Bakery 100 General Merchandise 125 Technology

Now let’s be a part of these two tables and alias every of the desk names. Enter the following SQL statement:

Try It

SELECT p.product_name, c.category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.category_id
WHERE p.product_name <> 'Pear';

There will be 5 archives selected. These are the outcomes that you ought to see:

product_name category_name Banana Produce Orange Produce Apple Produce Bread Bakery Sliced Ham Deli

In this example, we’ve created an alias for the products desk and an alias for the classes table. Now within this SQL statement, we can refer to the merchandise table as p and the categories desk as c.

When growing table aliases, it is now not necessary to create aliases for all of the tables listed in the FROM clause. You can select to create aliases on any or all of the tables.