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.

Let's look at an example of the SELECT statement that uses both table and column aliases.

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

Example

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 the below data.

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

Overall

We now understood how to use aliases in SQL statements.

Related Links