How to use aliases in SQL

Think of SQL aliases like nicknames. Suppose you have a friend named "Jonathan Vincent Richard." Instead of calling him by his full name every time, you simply call him "Jon." Similarly, SQL aliases are used to temporarily rename columns or tables for the duration of a query. Aliases make SQL queries more readable, concise, and easier to work with, especially when dealing with complex queries.

Aliases exist only for the duration of the query and don’t affect the actual column or table names in the database schema.

What are SQL aliases?

SQL aliases are temporary names given to columns or tables in SQL queries. They allow us to simplify references to these elements, making our SQL code more readable and manageable, especially when dealing with complex data operations or long table names.

Aliases are used in SELECT queries, WHERE clauses, JOIN conditions, and more. They don’t modify the original column or table names in the database; they only provide a shorthand for use within a query.

Syntax for column alias

The syntax for defining a column alias is simple. We use the AS keyword (optional) to assign a new name to a column in the result set. Here's how we can do it:

Syntax

SELECT column_name AS alias_name
FROM table_name;

  • column_name: The name of the column we want to alias.

  • alias_name: The temporary name we want to assign to the column.

  • AS: The keyword that assigns the alias (optional, can be omitted).

Example 1: Column Alias

Let's say we have a table called employees with a column named first_name. We can assign a column alias to make the result more user-friendly.

-- Step 1: Create the database
CREATE DATABASE company;
-- Step 2: Switch to the database
USE company;
-- Step 3: Create the employees table
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
-- Step 4: Insert sample data into the employees table
INSERT INTO employees (first_name, last_name, department, salary)
VALUES 
    ('Alice', 'Johnson', 'HR', 60000.00),
    ('Bob', 'Smith', 'Engineering', 75000.00),
    ('Charlie', 'Brown', 'Marketing', 50000.00),
    ('Diana', 'Garcia', 'Engineering', 82000.00),
    ('Eve', 'Martinez', 'Finance', 70000.00);
-- Step 5: Run the query to retrieve employee names with an alias
SELECT first_name AS "Employee Name"
FROM employees;
-- Exercise for Learners
-- Modify the query to include last names along with first names:
-- SELECT CONCAT(first_name, ' ', last_name) AS "Full Name" FROM employees;

This will display the results with the column header as "Employee Name" instead of first_name.

Modify or uncomment the query at line 31 to display full names instead of just first names by concatenating first_name and last_name.

Example 2: Without AS keyword

We can omit the AS keyword, and it will still work the same way.

-- Example 2: Without AS keyword for column alias
-- We can omit the AS keyword, and it will still work the same way.
-- Step 1: Create the database
CREATE DATABASE company;
-- Step 2: Switch to the database
USE company;
-- Step 3: Create the employees table
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
-- Step 4: Insert sample data into the employees table
INSERT INTO employees (first_name, last_name, department, salary)
VALUES 
    ('Alice', 'Johnson', 'HR', 60000.00),
    ('Bob', 'Smith', 'Engineering', 75000.00),
    ('Charlie', 'Brown', 'Marketing', 50000.00),
    ('Diana', 'Garcia', 'Engineering', 82000.00),
    ('Eve', 'Martinez', 'Finance', 70000.00);
-- Query: Retrieve employee names without using AS
SELECT first_name "Employee Name"
FROM employees;
-- Explanation:
-- In this case, the output will still show "Employee Name" as the column header.
-- Spaces in alias names: Use double quotes (" ") if the alias has spaces.
-- Case sensitivity: Aliases are typically case-insensitive unless enclosed in quotes,
-- which makes them case-sensitive in some databases (e.g., PostgreSQL).

In this case, the output will still show "Employee Name" as the column header.

Parameter explanation:

When using column aliases, there are a few important things to keep in mind:

  • Spaces in alias names: If we want to use an alias with spaces, enclose the alias in double quotes (" "). For example, "Employee Name".

  • Case sensitivity: By default, aliases are case-insensitive in most databases, but this behavior can change depending on the database system (e.g., PostgreSQL treats aliases in quotes as case-sensitive).

  • Using aliases in expressions: We can also create aliases for expressions, such as concatenating columns or performing mathematical operations.

Example 3: Alias for expressions

-- Example 3: Alias for expressions
-- Step 1: Create the database
CREATE DATABASE company;
-- Step 2: Switch to the database
USE company;
-- Step 3: Create the employees table
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
-- Step 4: Insert sample data into the employees table
INSERT INTO employees (first_name, last_name, department, salary)
VALUES 
    ('Alice', 'Johnson', 'HR', 60000.00),
    ('Bob', 'Smith', 'Engineering', 75000.00),
    ('Charlie', 'Brown', 'Marketing', 50000.00),
    ('Diana', 'Garcia', 'Engineering', 82000.00),
    ('Eve', 'Martinez', 'Finance', 70000.00);
-- Query: Concatenate first_name and last_name to create a "Full Name" column
SELECT CONCAT(first_name, ' ', last_name) AS "Full Name"
FROM employees;
-- Explanation:
-- The CONCAT function combines the first_name and last_name into one string.
-- The AS keyword creates a column alias named "Full Name."
-- This alias will appear as the header for the concatenated values.

This will display the concatenation of first_name and last_name as a new column named "Full Name".

While column aliases simplify results, table aliases are equally useful when working with multiple tables in JOIN queries.

Syntax for table alias

Just as we can create column aliases, we can also create aliases for tables. A table alias simplifies queries, especially when working with joins or complex queries.

Syntax:

SELECT column_name
FROM table_name AS alias_name;

  • table_name: The name of the table we want to alias.

  • alias_name: The temporary name we want to assign to the table.

Example 4: Table alias

Let’s say we have a table employees and we want to join it with another table, departments, using aliases to make the query easier to read.

-- Example 4: Table alias with AS keyword
-- Step 1: Create the database
CREATE DATABASE company;
-- Step 2: Switch to the database
USE company;
-- Step 3: Create the employees and departments table
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) -- Establish relationship
);
-- Step 4: Insert sample data into the departments and employees table
INSERT INTO departments (department_name) 
VALUES 
    ('HR'),
    ('Engineering'),
    ('Marketing'),
    ('Finance');
    
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES 
    ('Alice', 'Johnson', 1, 60000.00),
    ('Bob', 'Smith', 2, 75000.00),
    ('Charlie', 'Brown', 3, 50000.00),
    ('Diana', 'Garcia', 2, 82000.00),
    ('Eve', 'Martinez', 4, 70000.00);
-- Query: Use table aliases for employees and departments tables
SELECT e.first_name, d.department_name
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id;
-- Explanation:
-- e is the alias for the employees table.
-- d is the alias for the departments table.
-- These aliases make the query concise and easier to write, especially in joins.

Here, e is the alias for the employees table, and d is the alias for the departments table. These aliases make the query more concise and easier to write, especially when referencing multiple tables.

Example 5: Without AS for table alias

We can also create table aliases without using the AS keyword. This is common in many SQL queries.

-- Example 5: Table alias without AS keyword
-- Step 1: Create the database
CREATE DATABASE company;
-- Step 2: Switch to the database
USE company;
-- Step 3: Create the employees and departments table
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) -- Establish relationship
);
-- Step 4: Insert sample data into the departments and employees table
INSERT INTO departments (department_name) 
VALUES 
    ('HR'),
    ('Engineering'),
    ('Marketing'),
    ('Finance');
    
INSERT INTO employees (first_name, last_name, department_id, salary)
VALUES 
    ('Alice', 'Johnson', 1, 60000.00),
    ('Bob', 'Smith', 2, 75000.00),
    ('Charlie', 'Brown', 3, 50000.00),
    ('Diana', 'Garcia', 2, 82000.00),
    ('Eve', 'Martinez', 4, 70000.00);
-- Query: Table aliases without the AS keyword
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
-- Explanation:
-- Even without the AS keyword, the aliases e and d work the same way.
-- The query is functionally identical to Example 4.

Even without AS, the aliases e and d will still work the same way.

Key takeaways

  1. Column aliases: Use column aliases to rename fields for readability, especially when displaying results. Use table aliases to simplify complex queries, like JOIN operations, and reduce typing.

  2. Table aliases: Simplify queries, especially when dealing with multiple tables, by assigning shorter names to tables. This helps in complex queries like joins.

  3. Flexibility: Aliases can be used for expressions, making it easier to work with calculated fields in queries.

  4. Quoting aliases: If the alias contains spaces or special characters, enclose it in double quotes. Otherwise, it’s optional to use the AS keyword.

  5. No impact on database: Aliases don’t modify the actual column or table names in the database; they’re only for use in the current query.

1

Select the SQL query that shows all the columns and also creates a new column called total_price, which shows the result of multiplying quantity by unit_price for each order.

A)
SELECT order_id, product_name, quantity, unit_price, quantity * unit_price AS total_price  
FROM orders;
B)
SELECT order_id, product_name, quantity, unit_price AS total_price  
FROM orders;
C)
SELECT order_id, product_name, quantity * unit_price AS total_price  
FROM orders;
Question 1 of 20 attempted

Ready to learn SQL and become a database expert? Join our Learn SQL course and follow the path to becoming a Database Professional with SQL! Unlock your potential and gain the skills employers seek. Start your journey today!

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How to use alias in SELECT statement?

In a SELECT statement, we can use aliases to temporarily rename columns or tables for better readability. The syntax for creating a column alias is as follows:

SELECT column_name AS alias_name
FROM table_name;

What is the best practice of table alias in SQL?

The best practices for using table aliases in SQL include:

  • Short and clear: Choose short, meaningful aliases that clearly represent the table’s purpose (e.g., e for employees).
  • Consistency: Maintain consistent aliasing throughout queries. For example, always use e for employees and d for departments in all queries.
  • Avoid ambiguity: Ensure that the aliases are not confusing or ambiguous, especially when working with multiple tables.
  • Use descriptive aliases in complex queries: For complex queries with multiple joins, use descriptive aliases to make it easier for others to understand the query.

What is the benefit of using an alias in SQL?

Using aliases in SQL offers several benefits:

  • Readability: Aliases make queries easier to read and understand, especially with long or complex table and column names.
  • Simplified queries: Aliases help reduce the length of a query, especially when working with long column or table names or expressions.
  • Improved code maintenance: Aliases can make it easier to maintain and modify SQL queries in the future.
  • Better results presentation: Column aliases allow to display more user-friendly names in the result set, which can be helpful for reporting.

Why would you want to use a table alias in a query?

We would use a table alias in a query for the following reasons:

  • Simplification: When working with long table names, aliases provide a shorthand reference, making the query cleaner and easier to write.
  • Improved readability: In complex queries with multiple tables, using table aliases makes the query easier to read and understand.
  • Avoiding ambiguity: When joining multiple tables, table aliases help differentiate between columns with the same name from different tables.
  • Convenience in joins: In queries involving multiple joins, table aliases make it easier to reference columns from different tables and improve the query’s overall efficiency.

What is the difference between rename and alias in SQL?

  • Rename: Renaming in SQL involves changing the actual name of a column or table in the database schema. This is a permanent change and is done using ALTER commands, such as ALTER TABLE to rename a table or ALTER COLUMN to rename a column.
  • Alias: An alias, on the other hand, is a temporary name given to a column or table for the duration of a single query. It does not change the actual name in the database and only affects how the column or table is referred to in that specific query.
Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
YouTubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)