Summary -

In this topic, we described about the below sections -

SELECT AS statement used to rename the column name or table name temporary to provide security for original table. This is known as aliasing and it lasts until the existence of SELECT statement.

SELECT AS statement is used to create alias names for column or table by using with Select Statement.

Syntax to Rename for column - 

SELECT column_name1 AS alias_name1,
   column_name2 AS alias_name2,
    ..., 
   column_nameN AS alias_nameN
   FROM table_name;

Syntax to Rename for Table - 

SELECT column_name1,
   column_name2,
    ..., 
  Column_nameN
FROM table_name AS alias_table_name;

Points to note about SELECT AS statement are listed as below -

  • It renames the column or table temporary.
  • It lasts up to the existence of SELECT statement.
  • User cannot use the temporary name after the Select statement.
  • Creating temporary name in real time projects can be done by Database Administrator's.
  • Temporary table name is also known as correlation name.

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Renaming the column name in the result set.

Requirement - Renaming the emp_name as name in the result-set of the details from employee_details table. The query was as follows – 

SELECT emp_id, emp_name AS name, designation FROM employee_details;

By executing above query, we can get output like as shown in below –

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman

Scenario – Combine multiple columns and name it.

Requirement – Combine dept_id, dept_name with comma(,) separated and name it as Department with department_details table. The query was as follows – 

SELECT dept_id + ',' + dept_name AS Department FROM department_details;

By executing above query, we can get output like as shown in below –

Department
1000, Office
2000, Sales

Scenario – Get the data using aggregate function and name the result-set column.

Requirement – Get the total employees count from employee_details table and name the column as "Total employees". The query was as follows – 

 SELECT COUNT(emp_id) AS 'Total employees' FROM employee_details;

By executing above query, we can get output like as shown in below -

Total employees
9

Scenario – Rename the table.

Requirement - Rename the tables employee_details and department_details in the SELECT query. The query was as follows – 

SELECT e.emp_name, d. dept_name 
FROM employee_details as e, Department_table AS d 
WHERE dept_id = 1000;

By executing above query, we get output like as follows -

emp_name dept_name
Employee1 Office
Employee2 Sales
Employee3 Office
Employee4 Sales
Employee5 Office
Employee6 Office
Employee7 Office
Employee8 Sales
Employee9 Sales

When we use SELECT AS

  • When the table name is too big.
  • When more than one table used in the query like JOINs, UNIONs etc,.
  • When column name is large.
  • When aggregate functions are used in the query.
  • When the two columns are combined.
  • When there is no need to provide original name of the table to others.