How to Use Aliases in SQL?

Introduction

Ever felt like your SQL queries could use a bit of a readability boost? Enter SQL aliases. These nifty tools let you give your tables and columns temporary nicknames, making your queries clearer and easier to handle. This article will discuss all the use cases of alias clauses, like renaming columns and tables and combining multiple columns or subqueries.

Overview

• SQL aliases provide temporary nicknames for tables and columns to enhance query readability and manageability.
• SQL aliases, created using the AS keyword, simplify complex queries by allowing more intuitive table and column references.
• Examples include renaming columns in result sets, simplifying table names in joins, and combining multiple columns into one.
• Aliases are useful for renaming columns, shortening table names, and combining columns, improving query efficiency and readability.

What Are SQL Aliases?

SQL aliases are temporary names assigned to tables or columns in a query statement for better readability, clarity, and maintainability. This makes complex queries a bit easier to manage by giving you more meaningful references of tables and columns. An alias is usually created using the AS keyword. It can be very helpful during a result set column name replacement, joining table name simplification, or for combining multiple columns into one in an output.

Implementation of SQL Aliases

For Implementation purposes, we will use two tables, ‘employees’ and departments’:

-- Create the employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department INT,
    base_salary DECIMAL(10, 2),
    bonus DECIMAL(10, 2)
);

-- Insert data into the employees table
INSERT INTO employees (employee_id, first_name, last_name, department, base_salary, bonus) VALUES
(1, 'Ajay', 'Jaishwal', 1, 50000, 5000),
(2, 'Vijay', 'Singh', 2, 60000, 6000);

-- Create the departments table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- Insert data into the departments table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT');

Also read: SQL: A Full Fledged Guide from Basics to Advance Level

Use Cases of Alias

Here are the use cases of Alias:

Use Case 1: Alias for Columns

Using an alias for columns allows you to rename the columns in your query result. This is useful for readability or when the original column names are not descriptive enough.

For MySQL

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    department
FROM 
    Employees;

Using || (ANSI SQL standard, PostgreSQL, SQLite)

SELECT 
    first_name || ' ' || last_name AS full_name,
    department
FROM 
    employees;

Use Case 2: Alias for Tables

Aliases for tables are used to rename tables in your query. This is especially useful when you have long table names or perform self-joins (joining a table with itself).

-- Join employees with departments using table aliases
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM 
    employees AS e
JOIN 
    departments AS d
ON 
    e.department = d.department_id;

Use Case 3: Combining Multiple Columns into One

You can also use aliases while combining multiple columns into a single column in the result set. This can be done using concatenation or arithmetic operations.

Example:

Consider the employee’s table again. To combine the first name and last name into a single column and calculate the total salary from the base salary and bonus columns, you could use:

SELECT 
    concat(first_name ,last_name) AS full_name,
    base_salary + bonus AS total_compensation
FROM 
    employees;

Also read: SQL For Data Science: A Beginner Guide!

Conclusion

The Alias(AS) clause is very helpful in increasing the readability of queries, and you can use it in multiple places, such as columns and tables. You can use it when combining multiple columns or writing subqueries. These techniques are very useful to make queries very efficient and readable.

If you found this article helpful in understanding SQL Aliases then, comment below.

Frequently Asked Questions