Description

The SQL Aliases can be used to define a short name for a table or a table column within a SQL statement, that includes multiple tables.

  • They are temporary names, that exist only for the duration of the query.
  • They make table and column names more readable in the query.
  • They can be used in any SQL statements, like SELECT, UPDATE, DELETE, etc.,
  • They can be created using the AS keyword.

The column aliases are useful in defining a name to an output column in the result set, where the output can be a direct column value or generated using a function.

  • If the column name is too long or does not depict its content, then an alias can be used to define a proper name.
  • If the output column is a generated function output, then an alias can be used to define a proper name instead of the function itself.

We just have to provide short alias names for tables and refer to their columns using the alias name in the query.

In this chapter, let's look at some of its use cases.

Syntax

Here is the basic syntax of the SELECT statement using table aliases.

  • The table1_name and T1 represents the first table and its short alias name.
  • The table2_name and T2 represents the second table and its short alias name.
  • The short alias names are used to refer to their respective table columns, which makes it simpler.
SELECT T1.column1, T1.column2, T2.column1, T2.column2, ... 
FROM table1_name AS T1, table2_name AS T2
WHERE T1.column = T2.column;

Table Data

Before executing any queries, consider the below data on the tables.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

SELECT Statement Without Aliases

The below SELECT statement fetches data from multiple columns of the joined tables using INNER JOIN, without using aliases.

  • Actual table names are used to refer to its columns, as table aliases are not used.
  • Result set contains the actual column name, as column aliases are not used.

Run this on IDE

SELECT orders.order_id, orders.order_date, orders.order_value, customers.customer_name, shippers.shipper_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id
LEFT JOIN shippers ON orders.shipper_id = shippers.shipper_id;

After successful execution, the output contains below.

order_id order_date order_value customer_name shipper_name
10250 2014-07-05 1807.5 Dominique Perrier Speedy Express
10251 2014-07-15 1159 Fran Wilson United Package
10252 2014-07-18 2822 Maria Anders Federal Shipping
10253 2014-07-19 2575.3 Dominique Perrier United Package
10254 2014-07-24 1256.25 Maria Anders Federal Shipping
10255 2014-07-31 9247.5 NULL Speedy Express

SELECT Statement With Aliases

The below SELECT statement fetches data from multiple columns of the joined tables using INNER JOIN, with both table and column aliases.

  • Table aliases are used to define a temporary name to the tables used so that accessing their columns becomes easy.
  • Column aliases are used to define a custom name for each column in the output.

Run this on IDE

SELECT T1.order_id AS 'Order ID', T1.order_date AS 'Order Date', T1.order_value AS 'Order Value', T2.customer_name AS 'Customer Name', T3.shipper_name AS 'Shipper Name'
FROM orders AS T1
LEFT JOIN customers AS T2 ON T1.customer_id = T2.customer_id
LEFT JOIN shippers AS T3 ON T1.shipper_id = T3.shipper_id;

After successful execution, the output contains below.

Order ID Order Date Order Value Customer Name Shipper Name
10250 2014-07-05 1807.5 Dominique Perrier Speedy Express
10251 2014-07-15 1159 Fran Wilson United Package
10252 2014-07-18 2822 Maria Anders Federal Shipping
10253 2014-07-19 2575.3 Dominique Perrier United Package
10254 2014-07-24 1256.25 Maria Anders Federal Shipping
10255 2014-07-31 9247.5 NULL Speedy Express

Tips & Recommendations

SQL Aliases are allowed on the GROUP BY, ORDER BY, and HAVING clauses, but not allowed on the WHERE clause.

Overall

We now understood how to use aliases in SQL statements.

Related Links