What are SQL aliases?

With SQL aliases, you can temporarily assign an alternative name to a column or table for a query. This can, in many cases, help to improve the readability of the code.

What are SQL aliases used for?

While there are numerous SQL commands that are essential for creating and managing data in the Structured Query Language, the command for creating aliases primarily exists to make your life easier. You can create SQL aliases to temporarily rename a column or table. Aliases are usually shorter and simpler than the names they are temporarily replacing. Aliases only exist for the duration of the query and have no impact on the actual name in the database. You can initiate an alias in SQL using the keyword AS, although using the keyword is optional. In most cases, the use of aliases serves to improve the readability of code.

What is the syntax for SQL aliases?

The syntax of SQL aliases differs depending on whether you want to create an alias for a single column or an entire table. To assign an alias to a column, use the following command:

SELECT name_of_column AS alternative_name
FROM name_of_table;

In the code above, replace name_of_column with the name of the column as it appears in your database. For alternative_name, type the name that you want to temporarily use for the column. This will be the column’s alias. You also need to specify which table the column is in so the column can be found.

If you want to give an entire table an alias, use the following syntax:

SELECT name(s)_of_column(s)
FROM name_of_table AS alternative_name;

The syntax is similar. This time though, you need to select the column(s) in the table, specify the current name of the table and then assign an alias to it.

SQL alias examples

To illustrates how SQL aliases work and what benefits they offer, let’s create a small sample database that contains customer data:

CREATE TABLE Customers (
	CustomerID INT PRIMARY KEY,
	Name VARCHAR (20),
	City VARCHAR (20)
);

Now let’s add some data to it:

INSERT INTO Customers (CustomerID, Name, City)
VALUES (1, 'Wong', 'Sheffield'),
	(2, 'Giddens', 'Birmingham'),
	(3, 'Meyer', 'Glasgow'),
	(4, 'Kim', 'Cardiff'),
	(5, 'Smith', 'Aberdeen');

Next, we’re going to create a second table called ‘Orders’:

CREATE TABLE Orders (
	OrderNumber INT,
	Date DATETIME,
	CustomerID INT,
	Amount INT,
	PRIMARY KEY (OrderNumber)
);

Now, let’s add some values to this table:

INSERT INTO Orders VALUES
(138, '2024-01-17', 4, 20),
(139, '2024-01-18', 1, 5),
(140, '2024-01-27', 2, 19);

If we want to create an alias for this column, we can do so using the AS command. In the following example, we’re going to give the column ‘OrderNumber’ the alias ‘Number’:

SELECT OrderNumber AS Number
FROM Orders;

When using SQL aliases for tables, you can retrieve data from different tables and join them together. Here, we’re going to use the alias ‘C’ for the Customer table and ‘O’ for Orders:

SELECT C.CustomerID, C.Name, C.City, O.Amount
FROM Customers AS C, Orders AS O
WHERE C.CustomerID = B.CustomerID;

What alternatives to SQL aliases are there?

In some cases, a good alternative to SQL aliases is the database object SYNONYM, which can also be used to create simpler names. Unlike aliases though, you can use SYNONYM to exchange objects without negatively impacting the code.