In SQL, we have databases that contain tables where our data is present. If we want to select some data from this table then we make use of the SELECT clause with FROM clause specifying the table. We can apply some conditions to our selections using the WHERE clause.
Now some cases might be there where we are required to change the name of data selected i.e column name should be different from that of selecting table. So to achieve this task we are using AS clause with the SELECT clause to change the name of the column as desired.
This clause also provides a naming facility for aggregate function calls. Suppose if we called an aggregate function in some query and selected that one then we should provide some name for the column that stores it. To achieve this we will be using AS clause.
Let’s first set up our databases as shown below:
Now let’s create some tables and insert some data into them as given below:
Now we will be showing the working of SELECT AS in two different situations:
CASE 1: When we are required to select an aggregate function and name a column for it. Below the code given for it.
SELECT avg(salary1) AS average FROM table1;
The output of this query is shown below:
We can see how a new column is created with a name average that stores the result of the aggregate function. We can save this query with the new table name whose column is created above. If we do not use AS clause in the above query then this will happen as shown below:
We can see that the default format mentioned in the select clause is selected in the above query without AS clause.
CASE 2: When we are to select any column and rename it as desired. An example is given below:
If we are not using AS clause then the following will happen:
Hence, we are able to get an idea of AS clause which is used with the SELECT clause. We can also use it with FROM clause to rename the tables. This is done when there are correlated subqueries where two same tables are combined on one another in subqueries.