Esat Erkec
Using SQL AS keyword and JOIN clause.

SQL AS keyword overview and examples

February 11, 2020 by

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:

Using aliases for the tables can be very useful when a query involved a table more than once.

Syntax:

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.

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.

SQL AS keyword usage for the column names

Column alias example result

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.

Working with SQL Fiddle

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.

Executing query on SQL Fiddle

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)”

A query result without column heading

For this circumstance, we can use SQL AS keyword to specify an alias to this combined column.

Column alias usage with CONCAT_WS function

Using SQL AS keyword for the CONCAT_WS functions

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.

A query result without column heading

Now, we will give aliases to the column headings in the following query.

Using SQL AS keyword for the aggregate functions

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.

Using SQL AS keyword for the SQL date functions

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.

Using SQL AS keyword and JOIN clause.

The result set of the joined tables

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.

A query that does not  use alias

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

Esat Erkec
Latest posts by Esat Erkec (see all)
SQL commands, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. 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

915 Views