The AS keyword is used to give columns or tables a temporary name that can be used to identify that column or table later.
Example
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;
Here,
column_1, column_2,...column_nare the table columnsalias_1, alias_2,...alias_nare the aliases of the table columns
For example,
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.
For example,
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.
The 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.