AS keyword is used to give columns or tables a temporary name that can be used to identify that column or table later.
SELECT last_name AS name FROM Customers;
Here, the SQL command selects the last_name column from the Customers table. However, the column name is changed to name in the result set.
SQL AS Alias Syntax
The syntax of the SQL
AS command is:
SELECT column_1 AS alias_1, column_2 AS alias_2, …... column_n AS alias_n FROM table_name;
column_1, column_2,...column_nare the table columns
alias_1, alias_2,...alias_nare the aliases of the table columns
SELECT first_name AS name FROM Customers;
Here, the SQL command selects the first_name column of Customers. However, the column name will change to name in the result set.
SQL AS With More Than One Column
We can also use aliases with more than one column.
SELECT customer_id AS cid, first_name AS name FROM Customers;
Here, the SQL command selects customer_id as cid and first_name as name.
SQL AS With Expression
We can combine data from multiple columns and represent it in a single column using the
CONCAT() function. For example,
SELECT CONTACT(first_name, ' ', last_name) AS full_name FROM Customers;
Here, the SQL command selects first_name and last_name. And, the name of the column will be full_name in the result set.
However, our online compiler does not support the
CONCAT() function since it uses the SQLite library. Instead, you need to use the concatenation operator
|| to perform this task.
For example, here's an equivalent code that will run in our compiler.
-- concatenate first_name, empty space, and last_name into a single column named full_name in the result set SELECT first_name || ' ' || last_name AS full_name FROM Customers;
Here, the SQL command will concatenate the first_name and last_name columns in the result set as full_name.
Notice that we have also concatenated an empty space
' ' between first_name and last_name. This ensures that the data from these columns are separated by a space in the result set.
More SQL AS Examples
It's a common practice to use
AS to create aliases when working with functions. For example,
-- AS with functions SELECT COUNT(*) AS total_customers FROM Customers;
Here, the SQL command counts the total number of rows and represents the value as the total_customers attribute.
The result set of this command will have a total_customers column.
AS keyword can also be used to give temporary names to tables. For example,
-- AS table alias SELECT cu.first_name, cu.last_name FROM Customers AS cu;
Here, the SQL command temporarily names the Customers table as cu and selects first_name and last_name from cu.
The result set of this command will have first_name and last_name as columns.
We can use AS aliases with table names to make our snippet short and clean while working with
JOIN. For example,
SELECT C.customer_id AS cid, C.first_name AS name, O.amount FROM Customers AS C JOIN Orders AS O ON C.customer_id = O.customer_id;
Here, the SQL command temporarily names the Customers table as C and the Orders table as O and selects customer_id from C, first_name from C and amount from O.
The result set of this command will have cid, name and amount columns.
To learn more, visit SQL JOIN.