SQL Intermediate 6: AS - Aliasing

SQL

Vanessa Gottardi

5/11/20237 min read

In the world of databases and SQL, complex queries can often lead to confusing and unwieldy results. Our blog post explores the concept of aliasing, where you can assign temporary or alternative names to columns, tables, or expressions within your SQL queries. With examples and step-by-step explanations, you'll gain the skills to simplify your SQL queries and make data manipulation a breeze. Whether you're a seasoned SQL expert or just starting your SQL journey, this blog post will be a valuable addition to your database toolkit.

Hello everyone and welcome to SQL Intermediate 6: AS – Aliasing.

Even though it is not a super complicated topic and we have used it in previous exercises, it is good to better understand AS, especially when dealing with big data and multiple tables.

Structured Query Language (SQL) is the foundation of interacting with databases and manipulating data. It provides a powerful way to retrieve, update, and manage information in a structured manner. One of the essential concepts in SQL is aliasing, which allows us to assign temporary names to columns, tables, or expressions within a query. In this blog post, we will explore what aliasing is, why it is important, and how to use it effectively in SQL.

What is Aliasing in SQL?

Aliasing, in the context of SQL, is the process of assigning a temporary or alternative name to a table, column, or expression in a query.

It helps in simplifying the result set, making the output more readable, and improving the clarity of complex SQL queries. Instead of referring to columns or tables by their original names, you can use aliases to create more descriptive and meaningful names.

The Syntax of Aliasing in SQL

The syntax for aliasing in SQL varies slightly depending on the specific part you want to alias:

Alias for Columns in SQL:

In this syntax, we are retrieving data from the "table_name" table and giving the "column_name" a temporary name "alias_name" in the result set.

The original column name will not be shown in the output; instead, the result set will display the column with the alias name. This is particularly useful when we want to present data with more descriptive column names or to distinguish between multiple columns with similar names from different tables.

Alias for Tables in SQL (useful in self-joins and subqueries):

In this syntax, we are selecting data from the "table_name" table but using the alias "alias_name" for the table itself. This is particularly helpful in scenarios where we are performing self-joins or subqueries.

Self-joins occur when you join a table with itself (using different aliases for each occurrence) to relate rows within the same table. Subqueries are queries nested within the main query. By providing aliases to the tables, you can differentiate between each occurrence of the same table in the query, making it clear which data you are referencing.

Alias for Expressions (e.g., calculated columns):

In this syntax, we are using an "expression" to calculate a value and assigning it a temporary name "alias_name" in the result set.

The expression can involve one or more columns, constants, arithmetic operations, or functions. By giving the calculated result an alias, the output will display the calculated value with the specified name, making the result set more readable and comprehensible.

REMEMBERING…

SELECT: Used to retrieve data from a database table or perform calculations on it. It is one of the most fundamental and commonly used SQL commands. The SELECT statement allows you to specify the columns you want to include in the result set. You can select one or more columns, or even use aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on the data.

FROM: Used to specify the table or tables from which the data should be retrieved. It tells the database engine which table to read the data from while executing the SELECT statement. You can also use the FROM clause to join multiple tables together to retrieve data from related tables simultaneously.

Why Use Aliases in SQL?

Readability: Aliases provide more descriptive and human-readable names to the columns or tables. This makes it easier for developers, analysts, and stakeholders to understand the query's intent and results.

Avoiding Ambiguity: When you're dealing with complex queries involving multiple tables and columns with similar names, aliases help avoid ambiguity and potential naming conflicts.

Calculated Columns: Aliasing is useful when you want to create calculated columns or perform operations on existing columns. The alias allows you to give a meaningful name to the resulting value.

Self-Joins and Subqueries: When using self-joins or subqueries, you must alias the tables to distinguish between the different occurrences of the same table in the query.

Using our previous tables from SQL BASICS 1: How to Create Table and Add Data in SMSS, let’s get into some usage examples.

NOTE: Since we have small data, it might not sound very useful, however, when you start getting to larger data, doing many tables joins, it actually is very convenient and important.

Let’s start simple, just naming ‘FirstName’ column from ‘EmployeeDemographics’ table as ‘Given_Name’.

SELECT FirstName AS Given_Name FROM EmployeeDemographics -> Execute

This way, it is easier to understand which columns are coming from which tables, not only for you, but also for whoever you hand it off.

Aliasing is a powerful feature in SQL that allows us to enhance the readability and clarity of our queries. By providing temporary names to columns, tables, or expressions, we can make our code more human-friendly and avoid confusion in complex queries. Whether you are writing simple or sophisticated SQL statements, incorporating aliases will undoubtedly improve your query-writing skills and make your database interactions more efficient.

So, next time you write a SQL query, remember to give your data a friendly face with aliases!

AS - alias syntax in sql for expressions

NOTE: If we do not use AS, we can still name the column. Just add a space between the column and alias name.

SELECT FirstName Given_Name FROM EmployeeDemographics -> Execute

We can also combine FirstName and LastName just by adding a plus (+) after the FirstName, blank space between apostrophes (‘ ’) to give space between first and last name, plus (+) again, and LastName AS Full_Name FROM 'EmployeeDemographics'.

SELECT FirstName + ' ' + LastName AS Full_Name FROM EmployeeDemographics -> Execute

Now, let’s use aggregate functions starting by naming ‘EmloyeeDemograhics’ table as Demo and selecting employee ID.

SELECT Demo.EmployeeID FROM EmployeeDemographics AS Demo-> Execute

This is very important when we have a lot of joins or when we are selecting several columns with many joins because it can get very messy quick.

Next, let’s join ‘EmployeeSalary’ table, selecting employee ID and salary.

SELECT Demo.EmployeeID, Sal.Salary

FROM EmployeeDemographics AS Demo

JOIN EmployeeSalary AS Sal

ON Demo.EmployeeID = Sal.EmployeeID -> Execute

As we can see, query is clean and easy to understand comparing to writing the entire tables’ names, which is correct, however, very long and overwhelming to look at.

Now, let’s LEFT join our 3 tables using a bad example.

SELECT a.EmployeeID, a.FirstName, a.LastName, b.JobTitle, c.Age

FROM EmployeeDemographics AS a

LEFT JOIN EmployeeSalary AS b

ON a.EmployeeID = b.EmployeeID

LEFT JOIN EmployeeINFO AS c

ON a.EmployeeID = c.EmployeeID -> Execute

When we use letters to name tables, it does not give any context to the data we are referencing. Also, it gets very confusing to understand where those columns come from as we progress with the queries.

NOTE: Ages are NULL due to empty values in ‘EmployeesINFO’ table.

To give a better context t the previous query, let’s name it correctly.

SELECT Demo.EmployeeID, Demo.FirstName, Demo.LastName, Sal.JobTitle, Info.Age