SQL AS keyword is used to give an alias to table or column names in the queries. In this way, we can increase the readability and understandability of the query and column headings in the result set.
Introduction
If we aim to write more readable queries, using the short and concise aliases for the tables and columns will be very helpful in this objective. Some queries can be very complex and can contain a lot of joins, besides that incomprehensible table and column names make this issue more complicated and inconvenient. At this point, for such queries, code readability and understandability provide a noticeable benefit to make changes quickly by different developers. Otherwise, working with SQL queries that contain long and complex columns and table names can lead to consuming more energy.
Shortly, SQL AS keyword, in other words, using aliases for the column names generates a temporary name for the column headings and are shown in the result sets of the queries. This concept helps to generate more meaningful and clear column headings.
Syntax:
|
1 2 3 4 |
SELECT column_name_1 AS alias_name_1, column_name_2 AS alias_name_2, column_name_N AS alias_name_n FROM table_name; |
Using aliases for the tables can be very useful when a query involved a table more than once.
Syntax:
|
1 2 3 4 5 6 7 8 9 |
SELECT column_name_1, column_name_2, column_name_N FROM table_name AS table_alias; or SELECT column_name_1 AS alias_name_1, column_name_2 AS alias_name_2, column_name_N AS alias_name_n FROM table_name AS table_alias; |
Especially, this usage type is the best practice for the queries that involve join clauses. Now we will make some example in light of this information.
Preparing the data
Through the following query, we will generate two tables and we will populate some data. Also, you can practice this article’s examples in the SQL Fiddle easily. SQL Fiddle is a free tool that allows us to practice and test the SQL queries online without requiring any database and installation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
CREATE TABLE Customer_InformationListForSale (ID INT PRIMARY KEY, CustomerInfList_FirstName VARCHAR(100), CustomerInf_LastName VARCHAR(100), CustomerInf_Age INT, CustomerInf_Country VARCHAR(100) ); CREATE TABLE OrderTransaction_InformationListForSale (ID INT PRIMARY KEY, OrderTransaction_Date DATETIME, Customer_InformationListForSale_CustID INT, Amout FLOAT, FOREIGN KEY(Customer_InformationListForSale_CustID) REFERENCES Customer_InformationListForSale(ID) ); INSERT INTO [Customer_InformationListForSale] ([ID], [CustomerInfList_FirstName], [CustomerInf_LastName], [CustomerInf_Age], [CustomerInf_Country] ) VALUES (1, N'Lawrence', N'Williams', 23, N'Indonesia' ); INSERT INTO [Customer_InformationListForSale] ([ID], [CustomerInfList_FirstName], [CustomerInf_LastName], [CustomerInf_Age], [CustomerInf_Country] ) VALUES (2, N'Salvador', N'Smith', 56, N'U.S.A.' ); INSERT INTO [Customer_InformationListForSale] ([ID], [CustomerInfList_FirstName], [CustomerInf_LastName], [CustomerInf_Age], [CustomerInf_Country] ) VALUES (3, N'Ernest', N'Jones', 43, N'China ' ); INSERT INTO [Customer_InformationListForSale] ([ID], [CustomerInfList_FirstName], [CustomerInf_LastName], [CustomerInf_Age], [CustomerInf_Country] ) VALUES (4, N'Gilbert', N'Johnson', 56, N'Brazil ' ); INSERT INTO [Customer_InformationListForSale] ([ID], [CustomerInfList_FirstName], [CustomerInf_LastName], [CustomerInf_Age], [CustomerInf_Country] ) VALUES (5, N'Jorge', N'Brown', 56, N'India' ); INSERT INTO [OrderTransaction_InformationListForSale] ([ID], [OrderTransaction_Date], [Customer_InformationListForSale_CustID], [Amout] ) VALUES (1, CAST('13-Oct-1784' AS DATETIME), 1, 1903.12 ); INSERT INTO [OrderTransaction_InformationListForSale] ([ID], [OrderTransaction_Date], [Customer_InformationListForSale_CustID], [Amout] ) VALUES (2, CAST('15-May-1799' AS DATETIME), 2, 345690.12 ); INSERT INTO [OrderTransaction_InformationListForSale] ([ID], [OrderTransaction_Date], [Customer_InformationListForSale_CustID], [Amout] ) VALUES (3, CAST('13-Jun-1842' AS DATETIME), 3, 123.90 ); INSERT INTO [OrderTransaction_InformationListForSale] ([ID], [OrderTransaction_Date], [Customer_InformationListForSale_CustID], [Amout] ) VALUES (4, CAST('11-Dec-1880' AS DATETIME), 4, 8765 ); INSERT INTO [OrderTransaction_InformationListForSale] ([ID], [OrderTransaction_Date], [Customer_InformationListForSale_CustID], [Amout] ) VALUES (5, CAST('11-Nov-1828' AS DATETIME), 5, 17893.123 ); |
Giving aliases to columns using the SQL AS keyword
As mentioned, we can give an alias to the column names to make them more understandable and readable, also this alias does not affect the original column name and it is only valid until the execution of the query. In the following query, we will give FirstName alias to CustomerInfList_FirstName and LastName alias to CustomerInf_LastName.
|
1 2 3 4 5 |
SELECT CustomerInfList_FirstName AS FirstName, CustomerInf_LastName AS LastName FROM Customer_InformationListForSale |
Tip:
In this tip, we will demonstrate the previous example on SQL Fiddle. At first, we will open the link and the data preparing query will appear in the Schema Panel. We will click the Build Schema in order to create the table and populate the data.
After building the tables, we will see “Schema Ready” notification under the Schema Panel. As the last step, we will paste the example query and click the Run SQL button. So, we can execute the query and the result set will appear under the schema and the query panel.
After creating the test tables, we can paste the queries and execute them with the help of the Run SQL button. The result set will appear under page.
As you can see we used the AS keyword after the original column name and then we specified the alias. Therefore, the column headings have been changed with the alias and became more understandable.
For different cases, we may need to concatenate two different columns. However, if we don’t use an alias, the result set of the column heading will be “(No column name)”
|
1 2 |
SELECT CONCAT_WS(' ', CustomerInfList_FirstName, CustomerInf_LastName) FROM Customer_InformationListForSale |
For this circumstance, we can use SQL AS keyword to specify an alias to this combined column.
|
1 2 |
SELECT CONCAT_WS(' ', CustomerInfList_FirstName, CustomerInf_LastName) AS First_LastName FROM Customer_InformationListForSale |
The result set above shows that we can give an alias to the combined columns.
Most of the SQL built-in functions result does not return any column headings. For example, MIN, MAX, AVG, GETDATE, ABS, SQRT, DATEADD etc. functions act like this.
|
1 2 3 4 |
SELECT MAX(CustomerInf_Age), AVG(CustomerInf_Age), GETDATE() FROM Customer_InformationListForSale; |
Now, we will give aliases to the column headings in the following query.
|
1 2 3 4 |
SELECT MAX(CustomerInf_Age) AS CustomerMaximumAge, AVG(CustomerInf_Age) AS CustomerAverageAge, GETDATE() AS DateandTime FROM Customer_InformationListForSale |
If we wish to use space in the aliases, we must enclose the specified alias with quotes. In the following example, we will demonstrate this type of example.
|
1 2 3 4 |
SELECT MAX(CustomerInf_Age) AS "Customer Maximum Age", AVG(CustomerInf_Age) AS "Customer Average Age", GETDATE() AS "Date and Time" FROM Customer_InformationListForSale |
Giving aliases to tables using the SQL AS keyword
When we intend to use a table more than once in a query, we can shorten the table name through the AS syntax. In the following query, we will give Customer alias to Customer_InformationListForSale and CustomerOrders alias to OrderTransaction_InformationListForSale.
|
1 2 3 4 5 6 7 8 9 |
SELECT Customer.CustomerInfList_FirstName AS "First Name", Customer.CustomerInf_LastName AS "Last Name", CustomerOrders.Amout AS "Order Amount" FROM dbo.Customer_InformationListForSale AS Customer INNER JOIN dbo.OrderTransaction_InformationListForSale AS CustomerOrders ON Customer.ID = CustomerOrders.Customer_InformationListForSale_CustID |
As we can see that the table aliases have been placed after the FROM clause so we did not have to retype these long table names another time anywhere in the query. After the ON keyword, we used the alias of the tables.
If we don’t use the aliases the query text will be like as follows.
|
1 2 3 4 5 6 7 |
SELECT Customer_InformationListForSale.CustomerInfList_FirstName, Customer_InformationListForSale.CustomerInf_LastName, OrderTransaction_InformationListForSale.Amout FROM dbo.Customer_InformationListForSale INNER JOIN dbo.OrderTransaction_InformationListForSale ON Customer_InformationListForSale.ID = OrderTransaction_InformationListForSale.Customer_InformationListForSale_CustID |
Conclusion
In this article, we learned the basic usage of the SQL AS keyword and as evident from our demonstration above, it provides the following advantages:
- Improve query readability
- Degrade the complexity of the query
- Avoid striving to retype the long table names
- It allows us to give more meaningful column headings
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023