SQL Aliases | Academic Block

SQL ALIASES

Learn how to use SQL aliases to give tables and columns temporary names that make queries easier to read and understand.

What are SQL Aliases?

An SQL alias is a temporary name given to a table or column during a query. Aliases do not change the actual name stored in the database. They exist only for the duration of the query.

Aliases are especially useful when column names are long, when calculations need meaningful labels, or when working with multiple tables.

Column Alias Syntax

A column alias can be created using the AS keyword.

WITH employees(first_name, salary) AS (
  VALUES
    ('Aarav', 65000),
    ('Meera', 72000),
    ('Rohan', 58000)
)
SELECT first_name AS employee_name,
       salary AS annual_salary
FROM employees;

Alias Without AS

The AS keyword is optional in SQLite. You can also place the alias directly after the column name.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500)
)
SELECT name product_name,
       price product_price
FROM products;

Alias for Calculated Columns

Aliases are useful for giving calculated expressions meaningful names.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3),
    ('Monitor', 1500, 1)
)
SELECT name,
       price * quantity AS total_cost
FROM products;

Alias with Aggregate Functions

An alias can give a meaningful name to the result of an aggregate function such as COUNT(), SUM(), or AVG().

WITH sales(amount) AS (
  VALUES
    (1200),
    (800),
    (1500),
    (700)
)
SELECT SUM(amount) AS total_sales,
       AVG(amount) AS average_sale,
       COUNT(amount) AS number_of_sales
FROM sales;

Alias with GROUP BY

Aliases can be used to give aggregate results clear names when grouping records.

WITH employees(name, department, salary) AS (
  VALUES
    ('Aarav', 'IT', 65000),
    ('Meera', 'IT', 72000),
    ('Rohan', 'HR', 58000),
    ('Anika', 'HR', 62000)
)
SELECT department AS department_name,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Table Aliases

You can also assign a temporary name to a table. Table aliases are useful when table names are long or when the same table is referenced multiple times.

WITH employees(first_name, department) AS (
  VALUES
    ('Aarav', 'IT'),
    ('Meera', 'Finance'),
    ('Rohan', 'Sales')
)
SELECT e.first_name,
       e.department
FROM employees AS e;

Table Alias Without AS

Just like column aliases, SQLite allows the AS keyword to be omitted when creating a table alias.

WITH products(name, price) AS (
  VALUES
    ('Laptop', 60000),
    ('Tablet', 25000),
    ('Phone', 30000)
)
SELECT p.name,
       p.price
FROM products p;

Aliases with Multiple Tables

Table aliases become especially useful when querying more than one table. They make column references shorter and clearer.

WITH employees(id, name, department_id) AS (
  VALUES
    (1, 'Aarav', 10),
    (2, 'Meera', 20),
    (3, 'Rohan', 10)
),
departments(id, department_name) AS (
  VALUES
    (10, 'IT'),
    (20, 'Finance')
)
SELECT e.name AS employee_name,
       d.department_name AS department
FROM employees AS e
JOIN departments AS d
  ON e.department_id = d.id;

Aliases with JOIN

Short table aliases make JOIN queries easier to read, especially when several tables are involved.

WITH customers(id, customer_name) AS (
  VALUES
    (1, 'Aman'),
    (2, 'Neha')
),
orders(id, customer_id, amount) AS (
  VALUES
    (101, 1, 1200),
    (102, 2, 850),
    (103, 1, 500)
)
SELECT c.customer_name AS customer,
       o.amount AS order_amount
FROM customers AS c
JOIN orders AS o
  ON c.id = o.customer_id;

Using Aliases with ORDER BY

In SQLite, a column alias can be referenced in the ORDER BY clause. This can make queries with calculated columns easier to read.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3),
    ('Monitor', 1500, 1)
)
SELECT name,
       price * quantity AS total_cost
FROM products
ORDER BY total_cost DESC;

Aliases with WHERE

A column alias created in the SELECT list generally should not be used directly in the same query’s WHERE clause. Instead, repeat the expression or use a subquery.

WITH products(name, price, quantity) AS (
  VALUES
    ('Keyboard', 800, 2),
    ('Mouse', 400, 3),
    ('Monitor', 1500, 1)
)
SELECT name,
       price * quantity AS total_cost
FROM products
WHERE price * quantity > 1000;

Aliases with Subqueries

A subquery can be given a table alias and then queried like a temporary table.

SELECT p.product_name,
       p.total_cost
FROM (
  SELECT name AS product_name,
         price * quantity AS total_cost
  FROM (
    SELECT 'Keyboard' AS name, 800 AS price, 2 AS quantity
    UNION ALL
    SELECT 'Mouse', 400, 3
    UNION ALL
    SELECT 'Monitor', 1500, 1
  )
) AS p
WHERE p.total_cost > 1000;

Multiple Column Aliases

Multiple columns in the same query can have their own aliases.

WITH students(name, marks, subjects) AS (
  VALUES
    ('Aman', 420, 5),
    ('Neha', 455, 5),
    ('Rahul', 390, 5)
)
SELECT name AS student_name,
       marks AS total_marks,
       subjects AS subject_count,
       ROUND(CAST(marks AS REAL) / subjects, 2) AS average_marks
FROM students;

Aliases with COUNT

An alias can make the result of COUNT() easier to understand.

WITH orders(order_id, customer) AS (
  VALUES
    (101, 'Aman'),
    (102, 'Neha'),
    (103, 'Aman'),
    (104, 'Rahul'),
    (105, 'Aman')
)
SELECT customer AS customer_name,
       COUNT(*) AS order_count
FROM orders
GROUP BY customer
ORDER BY order_count DESC;

Quoted Aliases

If an alias contains spaces or special characters, it can be enclosed in double quotes in SQLite.

WITH products(name, price) AS (
  VALUES
    ('Keyboard', 800),
    ('Mouse', 400),
    ('Monitor', 1500)
)
SELECT name AS "Product Name",
       price AS "Product Price"
FROM products;

Common Uses of SQL Aliases

Alias Type	Example	Purpose
Column Alias	`name AS employee_name`	Rename a column in the result
Calculated Alias	`price * quantity AS total`	Name a calculated result
Table Alias	`employees AS e`	Shorten a table reference
Aggregate Alias	`COUNT(*) AS total`	Give an aggregate result a meaningful name

Advantages of SQL Aliases

Make query results easier to understand.
Provide shorter names for long table names.
Make calculated columns easier to identify.
Improve readability of JOIN queries.
Make aggregate results more descriptive.

Best Practices

Use meaningful aliases that clearly describe the data.
Use short table aliases in queries involving multiple tables.
Use AS when it improves readability.
Avoid aliases that are confusing or unrelated to the column.
Use quoted aliases when a result label needs spaces.

SQL aliases provide temporary names for columns, expressions, tables, and calculated results. They do not change the underlying database structure, but they can make SQL queries much easier to read, understand, and maintain.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.