SQL Column Alias

Introduction to SQL Column Alias

SQL Column Alias is the alternative name that can be assigned to any of the objects inside the SQL query statement that includes the names of the tables and columns that help in accessing and referring those objects with an alternative and small word that is an alias which makes it easy for specifying. We have to be careful while using aliases only about the name we use to specify the alias. The name of the alias should be such that it is meaningful and relevant to the object to which it is being assigned. The scope of the usage of the alias is only limited up to the query statement in which it is used.

Syntax and Usage:

name_of_column AS name_of_alias

Where name_of_column is the column of the table name to which we have to assign the alternative name and name_of_alias is the alternative or temporary name with which the table of the column to which it is assigned can be referred further in the query statement. We can use the aliases for columns as well as for tables.

Aliases to the Columns

Aliases are the alternative names that can be assigned to the values being retrieved from the query statement by specifying different columns and combinations of the columns. Aliases can also be assigned to the table names. The advantage of using the aliases is that shorter names can be assigned when the column names are large when multiple tables used in the query using different joins have same-named columns, the aliases of the table can help to determine the column of which table should be retrieved or applied the condition when the aggregated functions and other combinations of the column values operations are performed the retrieved value can be assigned a name instead of the whole expression performed which makes the resultant records read in meaningful and easier way for analysis.

Let us create one table named educba_learning using the following query statement.

Code:

CREATE TABLE educba_learning (
topic_id INT NOT NULL ,
subject VARCHAR(100) DEFAULT NULL,
sessions INT DEFAULT '0',
expert_name VARCHAR(100) DEFAULT NULL,
charges DECIMAL(7,2) DEFAULT '0.00')

Let us insert some records in the educba_learning table.

Code:

INSERT INTO educba_learning (topic_id, subject, sessions, expert_name, charges) VALUES
(1, 'SQL', '750', 'Payal Udhani', 75000),
(2, 'MySQL', '700', 'Siddharth Udhani', 35000),
(3, 'PostgreSQL', '600', 'Sonam Udhani', 45000),
(4, 'Hadoop', '980', 'Heena Udhani', 65000);

Output:

Let us first retrieve all the table’s records by simply using the following query statement that retrieves all the columns and rows of the table.

Code:

SELECT * FROM educba_learning;

Output:

Now, suppose we want to retrieve the records such that the results must contain the name of the subject and the charges per session. This can be calculated by simply dividing the charges column by the sessions column.

Code:

SELECT subject, charges/sessions FROM educba_learning;

Output:

Instead of charges/sessions, if we have to retrieve the column with some other name say “Charges Per Session” and of the subject as “Subject”, then aliases can be used as follows.

Code:

SELECT SUBJECT AS "Subject", charges/sessions AS "Charges Per Session" FROM educba_learning;

Output:

Let us consider one more example by referencing a table named educba_experts, whose contents are as shown below by the output of the following query statement.

Code:

SELECT * FROM educba_experts;

The output of the execution of the above query statement is as follows, containing the structure and contents of the table educba_experts.

Output:

As we can observe, the names of the columns expert_name and joining_date_time are too big, and while using them inside the query, we can assign the aliases for those columns and refer them by those aliases names. Consider one more table named educba_articles that has the topics written by the experts stored in the educba_experts table. The referencing of the expert in the educba_articles table is done by a column named expert_id, which stores the id value of table educba_experts in it to reference which article was being written by which expert. The structure and contents of the educba_articles table are as shown by the output of the following query statement.

Code:

SELECT * FROM `educba_articles`;

The output of the execution of the above query statement is a follows containing the structure and contents of the table educba_ articles.

Output:

Now, we want to retrieve the name of the article and the expert name and the joining date and time of the expert, along with the date on which the article was assigned and the status of the article. To do so, we will have to perform a join on the table educba_articles and educba_experts as the details of experts like name and joining date-time are stored in that table. Our query statement will be as shown below, in which we will use aliases for the columns to rename them while retrieving their values.

Code:

SELECT
educba_articles. ` name` AS "Article Name",
educba_articles. `assigned_date` AS "Date Of Assignment",
educba_experts. expert_name AS "Name Of Expert",
educba_experts. joining_date_time AS "Joining of Expert",
educba_articles. ` status` AS "Status"
FROM
educba_articles
JOIN educba_experts
ON educba_articles.expert_id = educba_experts.id ;

The output of the execution of the above query statement is a follows, containing the contents of both the tables and the columns that we chose to retrieve along with the modified headings of the columns as aliases are used.

Output:

Conclusion – SQL Column Alias

Aliases make the reference to the columns easier as they provide a facility to refer those objects by an alternative name for a temporary basis in the scope of the query statement. Aliases prove helpful when the name of the columns is too big or not that meaningful.

Recommended Articles

This is a guide to SQL Column Alias. Here we discuss the introduction and aliases to the columns with query statement, code and output respectively. You may also have a look at the following articles to learn more –

1. SQL NOT Operator
2. SQL Select Top
3. SQL DROP TRIGGER
4. SQL Merge Two Tables