How to Use Aliases in SQL?

Abhishek Kumar 10 Jul, 2024
4 min read

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

Q1. What is an alias in SQL?

Ans. An alias in SQL is a temporary name assigned to a table or column within a specific query. It acts like a nickname for the table or column, making the query easier to read and understand.

Q2. How to use alias variables in SQL?

Ans. SQL aliases don’t directly involve variables. You use the AS keyword followed by the desired alias name after the table or column you want to rename. For example:

SELECT CustomerName AS “Client Name” FROM Customers;
Here, CustomerName is aliased as "Client Name".

Q3. Why use a table alias?

Ans. There are several reasons to use table aliases:
A. Readability: When working with multiple tables with similar or long original names, aliases can provide shorter or more descriptive temporary names. This way, it is more readable.
B. Ambiguity Avoidance: If you are joining a table with columns that have the same name so, at that time, an Alias can be used to make some difference in those tables within the query.
C. Abbreviate: Using an alias is less cumbersome and allows the query to be written concisely than having long names for tables.

Q4. What is the use of an alias?

Ans. Aliases are used for both tables and columns in SQL. Here’s a breakdown of their uses:
Table Aliases:
A. Improve readability, especially with complex joins or multiple tables with similar names.
B. Avoid ambiguity when joining tables with columns that share the same name.
Column Aliases:
A. Make cryptic or long column names more understandable within the query.
B. Combine values from multiple columns into a single column with a meaningful alias.

Abhishek Kumar 10 Jul, 2024

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear