Skip to main content

Aliases (AS)

Learning Focus

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

Concept Overview​

Definition:

In MySQL, an alias is a temporary name assigned to a table or a column, using the AS keyword. It is commonly used to rename fields in query results or to simplify complex expressions.

Why It Matters:

  • Enhances readability of query outputs.
  • Simplifies referencing expressions or aggregate results.
  • Helps align column names with business reporting terminology.
  • Makes complex subqueries and joins easier to manage.

Where It Fits:

Aliases are used alongside:

  • SELECT statements
  • Filtering (WHERE, HAVING)
  • Aggregations (SUM, AVG, etc.)
  • Subqueries and views

Basic Syntax & Rules​

Column Alias Syntax​

SELECT column_name AS alias_name
FROM table_name;

Table Alias Syntax​

SELECT t.column_name
FROM table_name AS t;

Key Notes:​

  • The AS keyword is optional; you can use whitespace alone: SELECT column_name alias_name.
  • Table aliases must be used if referenced more than once or in joins.
  • Aliases exist only during query execution; they don't affect the actual table or column names.

Limitations:​

  • You can't use a column alias in the WHERE clause.
  • Aliases cannot contain reserved SQL keywords unless enclosed in backticks (`).

Step-by-Step Examples​

Sample Table: employees​

+----+------------+-----------+--------+----------+
| id | first_name | last_name | salary | dept_id  |
+----+------------+-----------+--------+----------+
| 1  | Alice      | Smith     | 70000  | 1        |
| 2  | Bob        | Jones     | 55000  | 2        |
| 3  | Carol      | Miller    | 80000  | 1        |
+----+------------+-----------+--------+----------+

Example 1: Simple Column Alias​

SELECT first_name AS 'First', last_name AS 'Last'
FROM employees;

Output:

+--------+--------+
| First  | Last   |
+--------+--------+
| Alice  | Smith  |
| Bob    | Jones  |
| Carol  | Miller |
+--------+--------+

Example 2: Aliases with Expressions​

SELECT first_name, salary * 1.10 AS adjusted_salary
FROM employees;

Output:

+------------+-----------------+
| first_name | adjusted_salary |
+------------+-----------------+
| Alice      | 77000.00        |
| Bob        | 60500.00        |
| Carol      | 88000.00        |
+------------+-----------------+

Example 3: Table Alias in Join​

Assume another table: departments

+----+-------------+
| id | dept_name   |
+----+-------------+
| 1  | Engineering |
| 2  | HR          |
+----+-------------+

SELECT e.first_name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

Output:

+------------+-------------+
| first_name | dept_name   |
+------------+-------------+
| Alice      | Engineering |
| Bob        | HR          |
| Carol      | Engineering |
+------------+-------------+

Practical Use Cases​

1. Report-Friendly Column Names​

Make outputs more user-friendly for non-technical stakeholders:

SELECT first_name AS 'First Name', salary AS 'Annual Salary'
FROM employees;

2. Renaming Aggregated Columns​

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

3. Joining Large Tables Efficiently​

SELECT e.first_name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

4. Use in BI Tools or Dashboards​

When integrating with tools like Tableau or Power BI, aliasing helps make column labels intuitive.

5. Simplifying Subqueries​

SELECT e.first_name
FROM (SELECT * FROM employees WHERE salary > 60000) AS e;

Common Mistakes & Troubleshooting​

Mistake 1: Using Alias in WHERE​

SELECT salary * 1.10 AS adjusted_salary
FROM employees
WHERE adjusted_salary > 70000;  -- [X] Error!

Fix:

Use the expression directly:

WHERE salary * 1.10 > 70000;

Mistake 2: Confusing Alias with Real Column​

An alias only exists within the query's scope and cannot be used in other clauses unless wrapped in a subquery.

Mistake 3: Forgetting Quotes for Space Names​

SELECT first_name AS First Name  -- [X] Error!

Fix:

SELECT first_name AS 'First Name';

Best Practices​

  • Use aliases to improve clarity, especially with joins and expressions.
  • Always quote aliases with spaces or special characters.
  • Avoid reserved keywords as aliases unless enclosed in backticks.
  • Don't overuse aliases when not necessary; they can make queries harder to follow.

Hands-On Practice​

Setup Sample Tables​

CREATE TABLE employees (
  id INT, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10,2), dept_id INT
);

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

Exercise 1 (Easy)​

Q: Display full names using alias Full Name.

-- Your Query Here

SELECT CONCAT(first_name, ' ', last_name) AS 'Full Name'
FROM employees;

Exercise 2 (Medium)​

Q: Show First Name and Adjusted Salary (salary + 5000).

SELECT first_name AS 'First Name', salary + 5000 AS 'Adjusted Salary'
FROM employees;

Exercise 3 (Hard)​

Q: Join with departments and show names with department titles.

SELECT e.first_name AS 'Name', d.dept_name AS 'Department'
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

Connection to Other Concepts​

Related ConceptsDescription
SELECTAliases are part of the output control in SELECT
JOINTable aliases simplify joins
WHERE/HAVINGImportant to note WHERE cannot use column aliases
SubqueriesAliases are essential for subquery referencing
GROUP BYAggregated columns are often aliased

Prerequisites: Basic SELECT, understanding tables and columns

What's Next: CASE statements, views, stored procedures

Visual Learning Diagram​

graph TD
  A[SELECT Statement] --> B[Expressions]
  A --> C[Column Filtering]
  C --> D[WHERE Clause]
  C --> E[HAVING Clause]
  B --> F[Aliasing with AS]
  F --> G[Renaming Columns]
  F --> H[Table Aliases]
  F --> I[Used in Joins]
  F --> J[Used in Subqueries]
  G --> K[Readable Outputs]
  H --> L[Shorter Join Syntax]
  I --> M[Multiple Table References]
  J --> N[Logical Query Division]
  F:::highlightTopic

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I,J,K,L,M,N allNodes
  class F stroke-width:4px

Common Pitfalls​

PitfallConsequencePrevention
Executing queries without validating sample rowsLogic errors reach production data or reportsStart with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behaviorAggregations and filters return misleading resultsTest with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schemaQueries fail due to missing columns/indexesVerify structure with DESCRIBE table_name; and adapt query design

Quick Reference​

SELECT first_name AS firstName FROM users;
SELECT COUNT(*) AS total_orders FROM orders;

What's Next​