SQLite - ALIAS Syntax

SQLite - Using Aliases

Aliases provide a temporary, alternative name for tables or columns in a specific query. They are essential for writing readable, maintainable SQL, especially in complex queries involving joins or aggregate functions.

Column Aliases

Column aliases are used to make the output of a query more readable or to give a name to a derived column (e.g., from a function or calculation).

Syntax

SELECT column_name AS alias_name
FROM table_name;

The AS keyword is optional but highly recommended for clarity.

Example

Using our EMPLOYEES table, we can give the first_name and last_name columns more descriptive aliases in the result set.

sqlite> SELECT first_name AS "First Name", last_name AS "Last Name", salary
   ...> FROM EMPLOYEES;

This query would produce headers ‘First Name’ and ‘Last Name’ instead of ‘first_name’ and ‘last_name’.

Table Aliases

Table aliases are primarily used to shorten long table names for conciseness and to distinguish between tables when joining them together.

Syntax

SELECT ...
FROM table_name AS alias_name
[JOIN another_table AS another_alias ON ...];

Modern JOIN Example with Aliases

Let’s revisit our EMPLOYEES and DEPARTMENTS tables. We want to list each employee’s full name and the name of their department. This requires a JOIN.

-- Sample Data
-- DEPARTMENTS: (1, 'Engineering'), (2, 'Finance')
-- EMPLOYEES: (1, 'Alice', ..., 1), (2, 'Bob', ..., 2)

Here is the query using modern JOIN syntax and table aliases (e for EMPLOYEES, d for DEPARTMENTS) for brevity and clarity.

sqlite> SELECT
   ...>    e.first_name,
   ...>    e.last_name,
   ...>    d.name AS department_name
   ...> FROM
   ...>    EMPLOYEES AS e
   ...> JOIN
   ...>    DEPARTMENTS AS d ON e.department_id = d.id;

This is much clearer than the old comma-based join syntax. The ON clause explicitly states the join condition. The aliases e and d make it easy to specify which table each column comes from.

The result would be:

first_name  last_name   department_name
----------  ----------  ---------------
Alice       Smith       Engineering
Bob         Johnson     Finance

Best Practices

• Use AS: Always use the AS keyword for aliases to improve readability.
• Be Consistent: Choose a consistent naming scheme for your aliases (e.g., the first letter of the table name).
• Use for Self-Joins: Table aliases are mandatory when joining a table to itself.
• Prefer Explicit JOIN: Always use the modern JOIN ... ON syntax instead of the old FROM table1, table2 WHERE ... syntax. It is less ambiguous and less prone to errors.