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 doesn't use 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, without using aliases.

  • Actual table names are used to refer to its columns, as table aliases are not used.
  • The 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 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 create a SELECT statement with JOINS without using aliases.

Related Links