Aliases (AS)

Learning Focus

Use this lesson to understand Aliases (AS) with practical syntax and examples.

Concept Overview

An alias is a temporary name you assign to a column/expression or a table reference inside a query.

You use aliases for two main reasons:

• Readability in output: rename columns in the result set (reports, dashboards)
• Readability in query text: shorten table references, especially in joins and self-joins

Why is it important?

• Clarity: a short table alias like o makes complex joins easier to read
• Correctness: qualifying columns with table aliases prevents ambiguous-column mistakes
• Maintainability: descriptive column aliases help downstream consumers (BI tools, CSV exports)

Where does it fit?

Aliases are used primarily in SELECT statements, especially with expressions, aggregates, joins, subqueries, and CTEs.

---

Syntax & Rules

Core Syntax

-- Column/expression alias
SELECT expression AS alias_name
FROM table_name;

-- Table alias
SELECT t.col
FROM table_name AS t;

Available Options / Parameters

Alias type	Form	Notes (PostgreSQL)
Column alias	expr AS alias	AS is optional: expr alias
Table alias	table AS t	AS is optional: table t
Quoted alias	AS "Full Name"	use double quotes for spaces/case

Key Rules and Considerations

• Aliases exist only for the duration of the query; they do not rename real columns or tables.
• A SELECT alias is not available in WHERE because WHERE is evaluated before the SELECT list.
• A SELECT alias is available in ORDER BY.
• PostgreSQL uses single quotes for string literals and double quotes for identifiers. If you need spaces in an alias, use double quotes.

---

Step-by-Step Examples

Example 1: Column Aliases for Report-Friendly Output (Beginner)

CREATE TABLE employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name  text NOT NULL,
  last_name   text NOT NULL,
  salary      numeric(12,2) NOT NULL CHECK (salary >= 0),
  dept_id     bigint NOT NULL
);

INSERT INTO employees (first_name, last_name, salary, dept_id) VALUES
  ('Alice', 'Smith', 70000, 1),
  ('Bob', 'Jones', 55000, 2),
  ('Carol', 'Miller', 80000, 1);

SELECT
  first_name AS first,
  last_name  AS last,
  salary     AS annual_salary
FROM employees
ORDER BY employee_id;

Expected output:

 first | last   | annual_salary
-------+--------+--------------
 Alice | Smith  | 70000.00
 Bob   | Jones  | 55000.00
 Carol | Miller | 80000.00
(3 rows)

Explanation:

• Column aliases rename the headers in the result.

---

Example 2: Aliases for Expressions (Intermediate)

SELECT
  first_name,
  salary,
  salary * 1.10 AS adjusted_salary
FROM employees
ORDER BY adjusted_salary DESC;

Expected output:

 first_name | salary   | adjusted_salary
------------+----------+----------------
 Carol      | 80000.00 | 88000.000000000000
 Alice      | 70000.00 | 77000.000000000000
 Bob        | 55000.00 | 60500.000000000000
(3 rows)

Explanation:

• Aliasing expressions is useful for calculations and for ordering by computed values.

---

Example 3: Table Aliases in Joins (Intermediate)

CREATE TABLE departments (
  dept_id   bigint PRIMARY KEY,
  dept_name text NOT NULL
);

INSERT INTO departments (dept_id, dept_name) VALUES
  (1, 'Engineering'),
  (2, 'HR');

SELECT e.first_name, d.dept_name
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.employee_id;

Expected output:

 first_name | dept_name
------------+-------------
 Alice      | Engineering
 Bob        | HR
 Carol      | Engineering
(3 rows)

Explanation:

• Table aliases (e, d) make it clear which table each column comes from.

---

Example 4: Alias Scope (WHERE vs ORDER BY) (Advanced)

You can use a SELECT alias in ORDER BY, but not in WHERE.

Wrong SQL:

SELECT salary * 1.10 AS adjusted_salary
FROM employees
WHERE adjusted_salary > 70000;

Typical error:

ERROR:  column "adjusted_salary" does not exist

Fix option A (repeat the expression):

SELECT salary * 1.10 AS adjusted_salary
FROM employees
WHERE salary * 1.10 > 70000
ORDER BY adjusted_salary DESC;

Fix option B (use a subquery/CTE):

WITH computed AS (
  SELECT employee_id, salary * 1.10 AS adjusted_salary
  FROM employees
)
SELECT employee_id, adjusted_salary
FROM computed
WHERE adjusted_salary > 70000
ORDER BY adjusted_salary DESC;

---

Practical Use Cases

1) Clean column names for dashboards

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS signups
FROM users
GROUP BY day
ORDER BY day;

2) Self-joins (aliases are required)

SELECT
  e.first_name AS employee,
  m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

3) Multi-join queries with consistent short aliases

SELECT o.order_id, c.customer_id, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status IN ('paid', 'shipped');

4) Labeling calculated fields

SELECT
  product_id,
  revenue_cents / 100.0 AS revenue_usd
FROM daily_product_revenue;

5) Subqueries that need a name

SELECT e.employee_id
FROM (
  SELECT employee_id
  FROM employees
  WHERE salary > 60000
) e;

---

Common Mistakes & Troubleshooting

1) Using single quotes for an alias

Wrong SQL:

SELECT first_name AS 'First Name'
FROM employees;

Typical error:

ERROR:  syntax error at or near "'First Name'"

Fix:

SELECT first_name AS "First Name"
FROM employees;

---

2) Using a SELECT alias in WHERE

Wrong SQL:

SELECT salary * 1.10 AS adjusted_salary
FROM employees
WHERE adjusted_salary > 70000;

Bad outcome:

• Fails because WHERE cannot see SELECT aliases.

Fix:

WHERE salary * 1.10 > 70000

---

3) Ambiguous column errors in joins

Wrong SQL:

SELECT dept_id
FROM employees
JOIN departments ON dept_id = dept_id;

Typical error:

ERROR:  column reference "dept_id" is ambiguous

Fix:

SELECT e.dept_id
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id;

---

4) Overusing short aliases reduces readability

Wrong approach:

• Aliasing every table as a, b, c in a multi-join query.

Fix:

• Use consistent, meaningful initials (u, o, p, evt) and keep them stable.

---

Debugging checklist:

1. Identify whether the alias is a column alias or a table alias.
2. Check scope: WHERE cannot use SELECT aliases; ORDER BY can.
3. For joins, qualify all columns that could exist in more than one table.
4. If an alias needs spaces/case, use double quotes.
5. If the query is hard to read, rename aliases to be more descriptive.

---

Best Practices

• ✅ Use short, consistent table aliases (u, o, p) in joins. ❌ Avoid random single-letter aliases in long queries.
• ✅ Use descriptive column aliases for computed fields (adjusted_salary, revenue_usd). ❌ Avoid unlabeled expressions that confuse downstream readers.
• ✅ Quote aliases only when necessary ("Full Name"). ❌ Avoid quoted aliases as a default; they are harder to type and case-sensitive.
• ✅ Qualify columns in joins (e.dept_id). ❌ Avoid relying on implicit resolution that can become ambiguous.
• ✅ Use CTEs/subqueries when you need to reuse an alias in filters. ❌ Avoid duplicating complex expressions everywhere.

---

Hands-On Practice

Use this setup for the exercises:

CREATE TABLE practice_employees (
  employee_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name  text NOT NULL,
  last_name   text NOT NULL,
  salary      numeric(12,2) NOT NULL,
  dept_id     bigint NOT NULL
);

INSERT INTO practice_employees (first_name, last_name, salary, dept_id) VALUES
  ('Alice', 'Smith', 70000, 1),
  ('Bob', 'Jones', 55000, 2),
  ('Carol', 'Miller', 80000, 1);

CREATE TABLE practice_departments (
  dept_id   bigint PRIMARY KEY,
  dept_name text NOT NULL
);

INSERT INTO practice_departments (dept_id, dept_name) VALUES
  (1, 'Engineering'),
  (2, 'HR');

Exercise 1 (Easy): Alias a full name

Task: Select first_name and last_name and output a single column aliased as full_name.

-- Your SQL here

Solution:

SELECT
  first_name || ' ' || last_name AS full_name
FROM practice_employees
ORDER BY employee_id;

---

Exercise 2 (Medium): Use a table alias in a join

Task: Join employees to departments using aliases and return first_name and dept_name.

-- Your SQL here

Solution:

SELECT e.first_name, d.dept_name
FROM practice_employees e
JOIN practice_departments d ON d.dept_id = e.dept_id
ORDER BY e.employee_id;

---

Exercise 3 (Advanced): Filter using a computed alias via a CTE

Task: Compute monthly_salary and return only rows where monthly_salary > 6000.

-- Your SQL here

Solution:

WITH computed AS (
  SELECT
    employee_id,
    salary / 12 AS monthly_salary
  FROM practice_employees
)
SELECT employee_id, monthly_salary
FROM computed
WHERE monthly_salary > 6000
ORDER BY employee_id;

---

Connection to Other Concepts

Concept	Why it matters
SELECT	aliases control output labels and readability
Joins	table aliases prevent ambiguity and simplify references
WHERE	alias scope rules explain why SELECT aliases are not visible
CTEs	let you reuse computed aliases safely
Aggregates	aggregate outputs are typically aliased (COUNT(*) AS total)

---

Visual Learning Diagram

flowchart TD
  A[SELECT] --> B[Expressions]
  A --> C[Joins]
  B --> D[Column Alias]
  C --> E[Table Alias]
  D --> F[ORDER BY Alias]
  D --> G[WHERE Scope Rule]
  G --> H[CTE/Subquery]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H allNodes
  class D highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using single quotes for aliases	syntax errors	use double quotes for identifiers with spaces
Using SELECT aliases in WHERE	column-not-found errors	repeat expression or use a CTE/subquery
Not qualifying columns in joins	ambiguous column errors	use table aliases and qualify columns
Overusing cryptic aliases	unreadable queries	use consistent, meaningful alias names
Quoted aliases everywhere	case-sensitive, awkward usage	quote only when necessary

---

Quick Reference

SELECT col AS alias FROM t;
SELECT expr AS computed FROM t ORDER BY computed;
SELECT t.col FROM table_name t;
SELECT col AS "Alias With Spaces" FROM t;
WITH x AS (SELECT expr AS a FROM t) SELECT * FROM x WHERE a > 0;

What's Next

• Previous: BETWEEN Operator - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.