Alias in SQL

Overview

• Alias: Assigns a temporary name to a column or table in SQL.
• Purpose: Increases readability and documentation in queries, especially beneficial for complex queries.

Key Points

1. What is an Alias?

   • A temporary name for a column or table.
   • Does not modify the original name, only used for the duration of the query.

2. Advantages of Using Alias

   • Enhances readability and documentation.
   • Assists in understanding query results.
   • Useful in complex queries involving joins and aggregate functions.

3. Syntax

   • Basic format: SELECT column AS AliasName FROM TableName
   • Optional use of the keyword AS.

4. Examples

   • Basic Usage:

     SELECT ID, Name, City FROM Student AS St WHERE Age > 18;
     

     • Here, St is an alias for the table Student.

   • Concatenation Example:

     SELECT FirstName || ' ' || LastName AS FullName FROM Employee;
     

     • Concatenates FirstName and LastName with a space, assigning the result the alias FullName.

   • Aggregate Function Example:

     SELECT AVG(Salary) AS AvgSal FROM Employee;
     

     • Calculates the average salary and gives the resulting column the alias AvgSal.

5. Examples of Alias Usage

   • Query Example without Alias:

     SELECT FirstName || LastName FROM Employee;
     

     • Results in unreadable output without spaces or a column name.

   • Improved Query with Alias and Space:

     SELECT FirstName || ' ' || LastName AS FullName FROM Employee;
     

     • Provides a readable column name FullName with proper format.

   • Handling Aggregate Functions:

     SELECT AVG(Salary) AS AvgSal FROM Employee;
     

     • Outputs AvgSal for the column containing average salary values.

   • Alias for Table:

     SELECT ID, Name, Department FROM Employee AS _temp;
     

     • The table Employee is referenced as _temp.

Conclusion

• Aliases can significantly enhance the clarity and maintainability of SQL queries, making them easier to understand and manage, especially when dealing with complex data transformations and aggregations.