SQL 28 🛢️ Column and Table Aliases with AS
Aliases give temporary names to columns and tables within a query. They exist only for the duration of the statement, do not modify the database schema, and serve two purposes: making output more readable and making complex queries easier to write and understand. A column alias renames the header in the result set; a table alias provides a short handle for referring to a table, which becomes essential when a query joins the same table to itself or when table names are long.
The AS keyword is the explicit syntax for aliasing in most databases, though it is optional for table aliases in many systems. Column aliases are frequently used to give computed columns meaningful names, to rename cryptic schema columns for display, and to resolve naming conflicts in joins. Table aliases are essential for self-joins, for correlated subqueries, and for keeping multi-table queries readable as the number of joined tables grows.
This chapter covers column aliases, table aliases, the AS keyword and where it can be omitted, self-joins with aliases, aliases in subqueries and derived tables, quoting rules for aliases with spaces or reserved words, and the cases where aliases cannot be referenced.
Key point: Aliases rename columns and tables for the duration of a query. Column aliases change result headers; table aliases provide short references for joins and subqueries. AS is optional for table aliases in most databases but recommended for clarity.
Why aliases exist
The readability problem. Schema column names are chosen for storage, not display. A column named cust_ln or order_ts is not appropriate as a report header. A column alias renames it to Customer Last Name or Order Timestamp for the query output without changing the table.
The computed column problem. Expressions in a SELECT list produce columns that have no natural name. SELECT price * quantity FROM order_items returns a column whose header is the expression itself in most databases, which is unusable. A column alias gives it a name: SELECT price * quantity AS line_total FROM order_items.
The self-join problem. When a table is joined to itself — to compare rows, to find hierarchical relationships, or to match pairs — the query must distinguish the two instances. Table aliases provide distinct names for each instance: FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id.
The verbosity problem. Long table names make queries hard to read and write. A table alias shortens them: FROM customer_order_line_items AS li allows li.quantity instead of customer_order_line_items.quantity. The alias also makes the query shorter and easier to scan.
The ambiguity problem. In a join, multiple tables may have columns with the same name. SELECT id FROM orders JOIN customers is ambiguous if both tables have an id column. Table aliases resolve this: SELECT o.id, c.id FROM orders o JOIN customers c. The qualifier o.id and c.id disambiguates.
a. Column aliases
A column alias renames a column in the result set. The syntax uses the AS keyword:
SELECT first_name AS given_name,
last_name AS family_name,
salary * 12 AS annual_salary
FROM employees;The AS keyword is optional in most databases for column aliases, but it is recommended for readability:
-- Works but less clear
SELECT first_name given_name, last_name family_name FROM employees;Column aliases apply only to the result set. They do not change the underlying column name, and they cannot be referenced in the WHERE clause because WHERE is evaluated before the SELECT list. They can be referenced in ORDER BY in most databases, because ORDER BY is evaluated after SELECT.
-- Alias in ORDER BY
SELECT first_name AS name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;
-- Alias NOT usable in WHERE
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000; -- error: column does not existTo filter on a computed value, repeat the expression in WHERE or wrap the query in a subquery or CTE.
-- Repeat the expression
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;
-- Or use a CTE
WITH emp AS (
SELECT salary * 12 AS annual_salary FROM employees
)
SELECT * FROM emp WHERE annual_salary > 100000;b. Table aliases
A table alias gives a table a short name within the query. The syntax is:
SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;The AS keyword is optional for table aliases in most databases:
FROM employees e
JOIN departments d ON e.department_id = d.department_id;Once a table is aliased, the original name cannot be used in the query. The alias replaces the name for all references in the statement. This is a common source of errors when mixing aliased and unaliased references.
-- Error: employees not defined after alias
SELECT employees.first_name
FROM employees e;The alias is required in the SELECT list and WHERE clause once it is declared. Consistency in using the alias improves readability and prevents errors.
c. Self-joins with aliases
A self-join joins a table to itself. Because the same table appears twice in the FROM clause, aliases are required to distinguish the two instances.
-- Find each employee and their manager's name
SELECT e.first_name AS employee,
m.first_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id;The alias e refers to the employee row, and m refers to the manager row. Without aliases, the query cannot distinguish which instance of employees is being referenced.
Self-joins are used for hierarchical data (employee-manager relationships, category-subcategory structures), for comparing rows within a table (finding duplicate records, comparing consecutive rows), and for finding pairs that satisfy a condition.
d. Aliases in subqueries and derived tables
A derived table is a subquery in the FROM clause. It must have an alias in most databases:
SELECT dept, avg_salary
FROM (
SELECT department AS dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_stats
WHERE avg_salary > 70000;The alias dept_stats names the derived table so the outer query can reference it. MySQL and PostgreSQL require this alias; some databases allow omitting it in certain contexts, but the practice is not portable.
In a correlated subquery, the inner query references a column from the outer query. Table aliases make this relationship explicit:
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);The inner query references e.department_id from the outer query, correlating the subquery with each row of the outer query.
e. Quoting aliases with spaces or reserved words
An alias that contains spaces, special characters, or a reserved word must be quoted. The quoting syntax varies by database.
Standard SQL and PostgreSQL use double quotes:
SELECT first_name AS "First Name",
last_name AS "Last Name"
FROM employees;MySQL uses backticks:
SELECT first_name AS `First Name`,
last_name AS `Last Name`
FROM employees;SQL Server uses square brackets:
SELECT first_name AS [First Name],
last_name AS [Last Name]
FROM employees;Reserved words used as aliases also require quoting. SELECT 1 AS order fails because order is reserved; SELECT 1 AS "order" works in PostgreSQL.
The recommendation is to avoid aliases with spaces and reserved words when possible. Use underscores or camelCase instead: first_name or firstName rather than "First Name". This keeps queries portable across databases.
f. Where aliases can and cannot be referenced
Alias availability depends on the logical order of SQL evaluation. The clauses are processed in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Column aliases are defined in the SELECT clause, so they are available to later clauses (ORDER BY) but not earlier ones (WHERE, GROUP BY, HAVING).
| Clause | Can reference column alias | Can reference table alias |
|---|---|---|
| SELECT | No (defining) | Yes |
| FROM | No | Yes (defining) |
| WHERE | No | Yes |
| GROUP BY | No (in most databases) | Yes |
| HAVING | No (in most databases) | Yes |
| ORDER BY | Yes | Yes |
Some databases, notably MySQL, allow column aliases in GROUP BY and HAVING. This is a non-standard extension and should not be relied upon for portable SQL.
Table aliases, by contrast, are defined in the FROM clause and are available in all subsequent clauses, including SELECT, WHERE, GROUP BY, HAVING, and ORDER BY.
Complete Example Session
-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INTEGER,
manager_id INTEGER,
salary NUMERIC(10, 2)
);
INSERT INTO departments VALUES
(1, 'Engineering'), (2, 'Sales'), (3, 'Marketing');
INSERT INTO employees VALUES
(101, 'Alice', 'Nguyen', 1, NULL, 95000),
(102, 'Bob', 'Martinez',1, 101, 78000),
(103, 'Carol', 'Okafor', 2, NULL, 82000),
(104, 'David', 'Kim', 2, 103, 65000),
(105, 'Eve', 'Petrov', 3, NULL, 72000);-- ============================================
-- PART 2: COLUMN ALIAS
-- ============================================
-- Rename columns in the result.
SELECT first_name AS given_name,
last_name AS family_name,
salary AS monthly_salary
FROM employees;-- ============================================
-- PART 3: COMPUTED COLUMN ALIAS
-- ============================================
-- Give expressions a meaningful name.
SELECT first_name,
last_name,
salary * 12 AS annual_salary
FROM employees;-- ============================================
-- PART 4: TABLE ALIAS
-- ============================================
-- Shorten table references in a join.
SELECT e.first_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;-- ============================================
-- PART 5: SELF-JOIN WITH ALIASES
-- ============================================
-- Compare rows in the same table.
SELECT e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;-- ============================================
-- PART 6: DERIVED TABLE WITH ALIAS
-- ============================================
-- Subquery in FROM requires an alias.
SELECT dept, avg_salary
FROM (
SELECT department_id AS dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;-- ============================================
-- PART 7: ALIAS IN ORDER BY
-- ============================================
-- Column aliases are available to ORDER BY.
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;-- ============================================
-- PART 8: ALIAS NOT AVAILABLE IN WHERE
-- ============================================
-- Column aliases cannot be used in WHERE.
-- Error:
-- SELECT salary * 12 AS annual_salary
-- FROM employees
-- WHERE annual_salary > 100000;
-- Correct: repeat the expression
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;-- ============================================
-- PART 9: QUOTED ALIAS WITH SPACES
-- ============================================
-- Use quotes for aliases with spaces.
SELECT first_name AS "First Name",
last_name AS "Last Name"
FROM employees;-- ============================================
-- PART 10: ALIAS IN CORRELATED SUBQUERY
-- ============================================
-- Outer alias referenced in inner query.
SELECT e.first_name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);These ten parts cover the complete range of alias usage: renaming columns, naming computed values, shortening table references, self-joins, derived tables, ORDER BY references, WHERE limitations, quoted aliases, and correlated subqueries. Each pattern addresses a different readability or disambiguation need.
Quick Reference
Alias Syntax
| Alias Type | Syntax | AS Required |
|---|---|---|
| Column alias | col AS name | Optional in most databases |
| Table alias | table AS t | Optional in most databases |
| Derived table | (...) AS name | Required in MySQL, PostgreSQL |
| Quoted alias | col AS "name" | Required for spaces, reserved words |
Quoting by Database
| Database | Quoting | Example |
|---|---|---|
| Standard SQL / PostgreSQL | Double quotes | AS "First Name" |
| MySQL | Backticks | AS `First Name` |
| SQL Server | Square brackets | AS [First Name] |
| SQLite | Double quotes | AS "First Name" |
Alias Availability by Clause
| Clause | Column Alias | Table Alias |
|---|---|---|
| SELECT | Defining | Yes |
| WHERE | No | Yes |
| GROUP BY | No (standard) | Yes |
| HAVING | No (standard) | Yes |
| ORDER BY | Yes | Yes |
Common Alias Patterns
| Pattern | Example |
|---|---|
| Rename column | salary AS monthly_salary |
| Name expression | price * quantity AS line_total |
| Shorten table | FROM employees AS e |
| Self-join | FROM employees e JOIN employees m |
| Derived table | FROM (...) AS stats |
| Correlated subquery | WHERE dept_id = e.dept_id |
Best Practices
✅ Do This:
SELECT first_name AS given_name FROM employees; -- AS for clarity
FROM employees AS e JOIN departments AS d -- Short aliases
ON e.department_id = d.department_id;
SELECT price * quantity AS line_total FROM items; -- Name expressions
FROM (SELECT ...) AS stats -- Alias derived tables
ORDER BY annual_salary; -- Alias in ORDER BY❌ Don’t Do This:
SELECT first_name AS "First Name"; -- ❌ Spaces in alias
SELECT e.first_name FROM employees; -- ❌ Unaliased after alias
SELECT ... WHERE annual_salary > 100; -- ❌ Alias in WHERE
SELECT 1 AS order; -- ❌ Reserved word unquotedCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Alias not found in WHERE | WHERE evaluated before SELECT | Repeat expression or use CTE |
| Table not found after alias | Original name used with alias defined | Use alias consistently |
| Derived table error | Missing alias on subquery | Add AS name to subquery |
| Quoting error | Wrong quote character for database | Use correct quoting style |
| Ambiguous column | Same column name in joined tables | Qualify with table alias |
| Reserved word alias | Using order, select, etc. | Quote or rename |
Real-World Examples
1. Rename Cryptic Columns
SELECT cust_ln AS "Last Name", cust_fn AS "First Name" FROM customers;2. Computed Column with Alias
SELECT product_name, price * quantity AS total FROM order_items;3. Join with Short Aliases
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;4. Self-Join for Hierarchy
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;5. Derived Table with Alias
SELECT * FROM (SELECT MAX(salary) AS max_sal FROM employees) AS t;6. Alias in ORDER BY
SELECT first_name, salary * 12 AS annual FROM employees ORDER BY annual DESC;7. Quoted Alias with Space
SELECT first_name AS "First Name" FROM employees;8. Correlated Subquery with Outer Alias
SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);9. CTE Instead of Alias in WHERE
WITH emp AS (
SELECT salary * 12 AS annual FROM employees
)
SELECT * FROM emp WHERE annual > 100000;10. Alias in GROUP BY (MySQL Extension)
SELECT YEAR(order_date) AS yr, COUNT(*) FROM orders GROUP BY yr;Visual
Column Alias vs Table Alias
┌──────────────────────────────────────────────────────────────┐
│ TWO TYPES OF ALIAS │
│ │
│ COLUMN ALIAS: │
│ SELECT salary * 12 AS annual_salary FROM employees; │
│ │ │ │
│ │ └── Renames result column │
│ └── Expression │
│ │
│ Result: │
│ ┌────────────────┐ │
│ │ annual_salary │ ← header changed │
│ ├────────────────┤ │
│ │ 1140000 │ │
│ │ 936000 │ │
│ └────────────────┘ │
│ │
│ TABLE ALIAS: │
│ SELECT e.first_name FROM employees AS e; │
│ │ │ │ │
│ │ │ └── Alias │
│ │ └── Table │
│ └── Qualifier │
│ │
│ The table is still "employees"; "e" is a short reference. │
└──────────────────────────────────────────────────────────────┘Self-Join with Aliases
┌──────────────────────────────────────────────────────────────┐
│ SELF-JOIN: SAME TABLE, TWO INSTANCES │
│ │
│ employees e employees m │
│ ┌────┬────────┬──────────┐ ┌────┬────────┬──────────┐ │
│ │ id │ name │manager_id│ │ id │ name │manager_id│ │
│ ├────┼────────┼──────────┤ ├────┼────────┼──────────┤ │
│ │101 │ Alice │ NULL │ │101 │ Alice │ NULL │ │
│ │102 │ Bob │ 101 │ │102 │ Bob │ 101 │ │
│ │103 │ Carol │ NULL │ │103 │ Carol │ NULL │ │
│ │104 │ David │ 103 │ │104 │ David │ 103 │ │
│ └────┴────────┴──────────┘ └────┴────────┴──────────┘ │
│ │
│ SELECT e.name AS employee, m.name AS manager │
│ FROM employees e │
│ LEFT JOIN employees m ON e.manager_id = m.id; │
│ │
│ Result: │
│ ┌──────────┬──────────┐ │
│ │ employee │ manager │ │
│ ├──────────┼──────────┤ │
│ │ Alice │ NULL │ │
│ │ Bob │ Alice │ │
│ │ Carol │ NULL │ │
│ │ David │ Carol │ │
│ └──────────┴──────────┘ │
└──────────────────────────────────────────────────────────────┘Alias Availability by Clause
┌──────────────────────────────────────────────────────────────┐
│ SQL EVALUATION ORDER AND ALIAS AVAILABILITY │
│ │
│ 1. FROM ← table aliases defined here │
│ │ │
│ ▼ │
│ 2. WHERE ← table aliases available │
│ │ column aliases NOT available │
│ ▼ │
│ 3. GROUP BY ← table aliases available │
│ │ column aliases NOT available (standard) │
│ ▼ │
│ 4. HAVING ← table aliases available │
│ │ column aliases NOT available (standard) │
│ ▼ │
│ 5. SELECT ← column aliases DEFINED here │
│ │ table aliases available │
│ ▼ │
│ 6. ORDER BY ← column aliases available │
│ table aliases available │
│ │
│ This is why column aliases work in ORDER BY but not WHERE. │
└──────────────────────────────────────────────────────────────┘Quoting Rules by Database
┌──────────────────────────────────────────────────────────────┐
│ HOW TO QUOTE ALIASES WITH SPECIAL CHARACTERS │
│ │
│ PostgreSQL / Standard SQL: │
│ SELECT 1 AS "First Name"; │
│ │
│ MySQL: │
│ SELECT 1 AS `First Name`; │
│ │
│ SQL Server: │
│ SELECT 1 AS [First Name]; │
│ │
│ SQLite: │
│ SELECT 1 AS "First Name"; │
│ │
│ Best practice: avoid spaces and reserved words in aliases. │
│ Use first_name or firstName instead. │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Column alias | Renames a result column |
| Table alias | Short reference for a table |
| AS keyword | Optional in most databases; recommended for clarity |
| Self-join | Requires table aliases to distinguish instances |
| Derived table | Subquery in FROM; alias required in most databases |
| Quoted alias | Required for spaces and reserved words |
| Column alias in WHERE | Not available (WHERE before SELECT) |
| Column alias in ORDER BY | Available (ORDER BY after SELECT) |
| Table alias scope | Available in all clauses after FROM |
| Quoting | Double quotes (standard), backticks (MySQL), brackets (SQL Server) |
Key takeaways:
- Column aliases rename output headers. They make reports readable and give computed columns meaningful names. They do not change the underlying schema.
- Table aliases shorten references. They are essential for self-joins, derived tables, and multi-table queries. Once defined, the original table name cannot be used.
- AS is optional for table aliases in most databases. It is required for derived tables in MySQL and PostgreSQL, and recommended for clarity everywhere.
- Column aliases are not available in WHERE. SQL evaluates WHERE before SELECT, so an alias defined in SELECT cannot be referenced in WHERE. Repeat the expression or use a CTE.
- Column aliases are available in ORDER BY. ORDER BY is evaluated after SELECT, so aliases are available there.
- Self-joins require aliases. When a table appears twice in the FROM clause, aliases distinguish the two instances.
- Aliases with spaces or reserved words must be quoted. The quoting syntax varies: double quotes for standard SQL and PostgreSQL, backticks for MySQL, brackets for SQL Server.
- Correlated subqueries use outer aliases. The inner query references the outer query’s table alias to correlate rows.
Remember: Aliases are temporary names that exist only for the duration of a query. They improve readability, resolve ambiguity, and enable self-joins and derived tables. Column aliases change result headers and are available in ORDER BY but not WHERE. Table aliases provide short references and are available throughout the query after the FROM clause. The AS keyword is optional but recommended for clarity. Quoting is required for aliases with spaces or reserved words, and the quoting style depends on the database. Understanding when aliases are available, and when they are not, prevents the common error of referencing a column alias in a WHERE clause and the common error of using the original table name after defining an alias.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!