DbSchema Database Designer


Publish on DbSchema Blog >>>

SQL, or Structured Query Language, is the standard language for managing and manipulating databases. As you dive deeper into the world of SQL, you’ll discover various techniques and tools that can make your queries more efficient and readable. One such tool is the SQL Alias. In this article, we’ll explore what SQL Aliases are, why they’re beneficial, and how to use them effectively with examples.

Introduction to SQL Aliases

SQL Aliases are temporary names assigned to a table or a column for the purpose of a particular SQL query. They are useful for providing a shorthand method of referencing columns or tables, especially if their names are long or cumbersome.


For a column:

SELECT column_name AS alias_name
FROM table_name;

For a table:

SELECT column_name
FROM table_name AS alias_name;

Benefits of SQL Aliases

  1. Readability: Makes SQL statements more readable especially when using joins and multiple tables.
  2. Efficiency: Reduces the amount of text you need to write, especially with complex queries.
  3. Avoiding Conflicts: Helpful when two tables have columns with the same names.

When to Use an SQL Alias

  1. Complex Queries: When working with multi-table queries or complex SQL queries.
  2. Aggregated Data: When using aggregate functions like SUM, COUNT, etc.
  3. Subqueries: With subqueries in the FROM clause.
  4. Self-Joins: When a table is joined to itself.

Column Alias

Column aliases are used to rename a column’s header in the result set.


SELECT column_name AS alias_name
FROM table_name;


Suppose we have a table named Students:

StudentID FirstName LastName
1 John Doe
2 Jane Smith


SELECT FirstName AS First, LastName AS Last
FROM Students;


First Last
John Doe
Jane Smith


Here, “FirstName” is displayed as First and LastName as Last.

Table Alias

Table aliases are used to provide a table with a temporary name, which can make queries shorter.


SELECT column_name
FROM table_name AS alias_name;


Suppose we have a table named Orders:

OrderID ProductName Quantity
1 Apple 10
2 Banana 5


SELECT o.OrderID, o.ProductName
FROM Orders AS o;


OrderID ProductName
1 Apple
2 Banana


Here, the table Orders is referenced as o in the query.

Aliases in Complex Queries

Aliases are particularly beneficial when dealing with complex queries involving multiple tables.


Given two tables Employees and Departments.


EmpID Name DeptID
1 Alice 101
2 Bob 102


DeptID DeptName
101 HR
102 Finance


SELECT e.Name, d.DeptName
FROM Employees AS e, Departments AS d
WHERE e.DeptID = d.DeptID;


Name DeptName
Alice HR
Bob Finance


Here, we join the two tables using their respective aliases.

Alias with Aggregate Functions

When using aggregate functions, aliases can provide a clearer column name for the result.


Given a table Sales:

SaleID Amount
1 100
2 150


SELECT SUM(Amount) AS TotalSales
FROM Sales;




Here, the sum of all sales is displayed under the column TotalSales.

Alias with Subqueries

Aliases are crucial when using subqueries, especially in the FROM clause.


Suppose we have a table Products:

ProdID Price
1 50
2 100


SELECT AVG(s.Price) AS AveragePrice
FROM (SELECT Price FROM Products WHERE Price > 50) AS s;




The sub-query fetches products with a price greater than 50, and the outer query calculates the average of those prices.

Using an Alias with a Self-Join

Self-joins are joins in which a table is joined with itself. Aliases are essential here to distinguish between the two instances of the table.


Given a table Employees:

EmpID Name ManagerID
1 Alice NULL
2 Bob 1


SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees AS e1, Employees AS e2
WHERE e1.ManagerID = e2.EmpID;


Employee Manager
Bob Alice


Here, Bob’s manager is Alice.

Using SQL Alias with JOIN

Using aliases can make JOIN operations more readable.


Given two tables Users and Orders.


UserID UserName
1 John
2 Jane


OrderID UserID Product
1 1 Laptop
2 2 Book


SELECT u.UserName, o.Product
FROM Users AS u
JOIN Orders AS o ON u.UserID = o.UserID;


UserName Product
John Laptop
Jane Book


Here, we fetched products ordered by each user.

Using an Alias with Non-Unique Column Names

When two tables have columns with the same name, aliases can help differentiate them.


Given two tables Teachers and Students, both having a column “Name”.

Using the alias:

SELECT t.Name AS TeacherName, s.Name AS StudentName
FROM Teachers AS t, Students AS s
WHERE t.ClassID = s.ClassID;


This way, we can clearly differentiate between teacher names and student names in the result set.

Common Mistakes

  1. Forgetting the AS Keyword: While the AS keyword is optional in many databases, it’s a good practice to use it for clarity.
  2. Not Using Aliases in Joins: This can lead to confusion when tables have columns with the same name.
  3. Inconsistent Alias Usage: Once you set an alias, ensure you use it consistently throughout the query.


Q1: Can I use SQL aliases outside the query they are defined in?

  • Answer: No, aliases are temporary and can only be used within the query they are defined in.

Q2: Can I use numerical values as aliases?

  • Answer: Yes, but it’s not recommended as it can lead to confusion. Always aim for meaningful alias names.

Q3: Can two tables in the same query have the same alias?

  • Answer: No, aliases within a query must be unique.

Practice Questions

  1. Given a table Products, write a query to fetch the total price of all products using an alias TotalPrice.
  2. Write a query to fetch the first name and last name from a table Persons as a single column alias FullName.
  3. Using a table Books, write a query to fetch the average price of all books with a price greater than 50 using a sub-query and an alias AvgPriceAbove50.
  4. Given two tables Authors and Books, write a query using aliases to fetch the name of the author and the title of the book they wrote.

Happy querying!

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.