sql_alias

SQL Aliases

SQL aliases are used to assign a temporary, alternative name to a table or a column in a query. This can make your SQL statements shorter, more readable, and easier to understand, especially in complex queries involving joins or functions.

Understanding SQL Aliases

Aliases are temporary names given to tables or columns for the duration of a particular SQL query. They do not change the actual names in the database schema.

Benefits of using aliases:

• Readability: Makes complex queries easier to read by shortening long or cryptic names.
• Clarity in Joins: Essential when joining a table to itself (self-join) or when multiple tables have columns with the same name.
• Calculated Columns: Provides meaningful names for columns derived from expressions or functions.
• Conciseness: Reduces the amount of typing needed.

SQL Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name;

Or, in some database systems, the AS keyword is optional for column aliases, though it’s good practice to include it for clarity:

SELECT column_name alias_name
FROM table_name;

SQL Alias Syntax for Tables

SELECT column_name(s)
FROM table_name AS alias_name;

Similarly, the AS keyword is often optional for table aliases, but recommended:

SELECT column_name(s)
FROM table_name alias_name;

Demo Database

We’ll use conceptual “Customers” and “Orders” tables for our examples, similar to those in the Northwind database.

Sample “Customers” table data:

CustomerID | CustomerName                        | ContactName     | Address                        | City        | PostalCode | Country
---------- | ----------------------------------- | --------------- | ------------------------------ | ----------- | ---------- | -------
1          | Alfreds Futterkiste               | Maria Anders    | Obere Str. 57                  | Berlin      | 12209      | Germany
2          | Ana Trujillo Emparedados y helados  | Ana Trujillo    | Avda. de la Constitución 2222  | México D.F. | 05021      | Mexico
3          | Antonio Moreno Taquería             | Antonio Moreno  | Mataderos 2312                 | México D.F. | 05023      | Mexico
4          | Around the Horn                     | Thomas Hardy    | 120 Hanover Sq.                | London      | WA1 1DP    | UK

Sample “Orders” table data:

OrderID | CustomerID | EmployeeID | OrderDate  | ShipperID
------- | ---------- | ---------- | ---------- | ---------
10354   | 58         | 8          | 1996-11-14 | 3
10355   | 4          | 6          | 1996-11-15 | 1
10356   | 86         | 6          | 1996-11-18 | 2

Alias Example for Columns

The following SQL statement renames CustomerName to Customer and ContactName to Contact Person in the output. If an alias contains spaces, it must be enclosed in double quotes or square brackets (syntax may vary by RDBMS).

SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;

This query will produce columns named ‘Customer’ and ‘Contact Person’.

Creating an alias for a concatenated column:

SELECT CustomerName, CONCAT(Address, ', ', City, ', ', PostalCode, ', ', Country) AS FullAddress
FROM Customers;

Note: The CONCAT() function is standard for string concatenation. Some databases like SQL Server use + for string concatenation, while others like Oracle or PostgreSQL use ||.

Alias Example for Tables

Table aliases are particularly useful in JOIN operations. The following SQL statement selects all orders for the customer ‘Around the Horn’. We use aliases c for “Customers” and o for “Orders” to shorten the query:

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
WHERE c.CustomerName = 'Around the Horn';

Without aliases, the query would be more verbose:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerName = 'Around the Horn';

When are aliases most helpful?

• When querying multiple tables with JOIN clauses.
• When using functions or expressions to create derived columns.
• When column names are long, uninformative, or contain characters that are awkward in SQL.
• When performing self-joins (joining a table to itself).
• When multiple tables in a join share the same column names (e.g., ID, Name), aliases are crucial for disambiguation.