As Clause Is Used in SQL For Operations, Usage with Practical Example
Summary: You will learn in this article, SQL AS keyword and Why as the clause is used in SQL, Database Management System. Let’s understand the SQL AS keyword.
How to used ‘AS’ clause in SQL ?
‘As’ clause is used for Rename operation. The AS clause is used to rename a column or table with an alias. Therefore, An alias only exists for the period or duration of the query.
Introduction of SQL AS Clause
SQL AS command is used to provide an alias to table or column names in the queries. during this means, we can increase the readability and understandability of the query and column headings within the result set.
If we aim to write more readable queries, then you can be using short and concise aliases for the tables and columns will be most helpful in this objective. Few queries can be very difficult or more complex and can contain a lot of joins, besides that incomprehensible table & column names create this issue additional difficult and inconvenient.
At this time, for such queries, code readability and quality offer an obvious profit benefit to make changes quickly by different developers. Furthermore, working with SQL queries that contain long and complicated columns and table names can lead to consuming additional or more energy.
Shortly, SQL AS keyword, in different words using aliases for the column names generates a temporary or short-lived name for the column headings and is shown within the result sets of the queries. This idea helps to generate more meaningful and clear column headings.
Syntax:
|
1 2 3 4 5 |
SELECT ColumnName_1 AS AliasName_1, ColumnName_2 AS AliasName_2, ColumnName_3 AS AliasName_3, columnName_N AS AliasName_N FROM Table_Name; |
When you will used aliases for the tables can be most helpful when a query involved a table more than once.
Syntax:
|
1 2 3 4 5 6 7 8 9 |
SELECT ColumnName_1, ColumnName_2, columnName_N FROM Table_Name AS Table_Alias; or SELECT ColumnName_1 AS AliasName_1, ColumnName_2 AS AliasName_2, ColumnName_N AS AliasName_N FROM Table_Name AS Table_Alias; |
Furthermore especially, this usage type is a good practice for the queries that involve SQL joining clauses. we’ll build some examples in light of this info.
Preparing The Data To Understand AS Keyword
As per the given below the following query, we will create two tables and we will populate some information or data. Also, you’ll be able to follow this article’s examples within the SQL Fiddle simply. SQL Fiddle is a free tool that allows us to practice and test the SQL queries online without the need for any database and installation.
First Step: Create Table
Table 1: Customer_InfoForDeal
|
1 2 3 4 5 6 7 |
CREATE TABLE Customer_InfoForDeal (ID INT PRIMARY KEY, CustomerInfoDealList_FirstName VARCHAR(100), CustomerInfo_LastName VARCHAR(100), CustomerInfo_Age INT, CustomerInfo_Country VARCHAR(100) ); |
Table 2: OrderPayment_InfoForDeal
|
1 2 3 4 5 6 7 |
CREATE TABLE OrderPayment_InfoForDeal (ID INT PRIMARY KEY, OrderPayment_Date DATETIME, Customer_InfoForDeale_CustID INT, Amout FLOAT, FOREIGN KEY(Customer_InfoForDeale_CustID) REFERENCES Customer_InfoForDeal(ID) ); |
2nd Step: Insert Records in Both Table
Table 1: Insert Records in Table Customer_InfoForDeal
|
1 2 3 4 5 |
INSERT INTO Customer_InfoForDeal VALUES (11, 'Aditya', 'Shukla', 30, 'India'); INSERT INTO Customer_InfoForDeal VALUES (12, 'Shivaditya', 'Kumar', 15, 'US'); INSERT INTO Customer_InfoForDeal VALUES (13, 'Saumay', 'Tripathi', 29, 'France'); INSERT INTO Customer_InfoForDeal VALUES (14, 'Pratima', 'Dubey', 31, 'India'); INSERT INTO Customer_InfoForDeal VALUES (15, 'Anoop', 'Dubey', 36, 'Canada'); |
Table 2: Insert Records in Table OrderPayment_InfoForDeal
|
1 2 3 4 5 |
INSERT INTO OrderPayment_InfoForDeal VALUES (11, CAST('11-Oct-2018' AS DATETIME), 11, 1903.12); INSERT INTO OrderPayment_InfoForDeal VALUES (12, CAST('14-Nov-2018' AS DATETIME), 12, 2800.00); INSERT INTO OrderPayment_InfoForDeal VALUES (13, CAST('16-Dec-2018' AS DATETIME), 13, 1199.00); INSERT INTO OrderPayment_InfoForDeal VALUES (14, CAST('18-Jan-2019' AS DATETIME), 14, 1671.72); INSERT INTO OrderPayment_InfoForDeal VALUES (15, CAST('21-Feb-2019' AS DATETIME), 15, 3478.16); |
Using The SQL AS keyword Giving Aliases To Columns
Therefore, we can give an alias to the column names to make them more readable and understandable also this alias doesn’t affect the original column name and it’s only valid until the execution of the query. Furthermore, In the given below following query, we will give FirstName alias to CustomerInfoDealList_FirstName and LastName alias to CustomerInfo_LastName.
|
1 2 3 4 5 |
SELECT CustomerInfoDealList_FirstName AS FirstName, CustomerInfo_LastName AS LastName FROM Customer_InfoForDeal |
Using The SQL AS keyword Giving Aliases To Tables
When we will use a table more than once in a query, then we can shorten the table name through the SQL AS syntax. Therefore, in the following query, we will give Customer alias to Customer_InfoForDeal and CustomerOrders alias to OrderPayment_InfoForDeal.
|
1 2 3 4 5 6 7 8 9 |
SELECT Customer.CustomerInfoDealList_FirstName AS "First Name", Customer.CustomerInfo_LastName AS "Last Name", CustomerOrders.Amout AS "Order Amount" FROM dbo.Customer_InfoForDeal AS Customer INNER JOIN dbo.OrderPayment_InfoForDeal AS CustomerOrders ON Customer.ID = CustomerOrders.Customer_InfoForDeale_CustID |
Therefore, as we can see in the above example that the table aliases have been placed after the FROM keyword so we didn’t have to retype these long table names another time anywhere in the query. After the SQL ON keyword, we tend to use the alias of the tables.
Thus, if we don’t use the aliases the query text will be as follows.
|
1 2 3 4 5 6 7 |
SELECT Customer_InfoForDeal.CustomerInfoDealList_FirstName, Customer_InfoForDeal.CustomerInfo_LastName, OrderPayment_InfoForDeal.Amout FROM dbo.Customer_InfoForDeal INNER JOIN dbo.OrderPayment_InfoForDeal ON Customer_InfoForDeal.ID = OrderPayment_InfoForDeal.Customer_InfoForDeale_CustID |
AS Clause Related FAQS
What is purpose of the SQL AS clause?
The AS keyword in SQL is used to change the column name in the output or assign a name to a derived column.
Which clause is used to rename operations?
The AS command is used for rename operations, attributes in the relations in the from clause. result. Hence, SQL Server provides a way or method of renaming the attributes of a result relation.
Do you need to use AS in SQL?
In Standard SQL Server they’ll have an optional AS clause however it shouldn’t be used because it may give the impression that something is being renamed when it isn’t. Furthermore, in fact, it should be omitted to enforce the point that it’s a range variable.
Which is AS clause used for?
AS keyword is used in: DEFINE statements, to name a record-set that will later be referenced by another statement like SQL SELECT or FROM clause.
What is rename operation in SQL?
The RENAME operation in SQL is used to rename the output of a relation.
Sometimes it’s straightforward and appropriate to break a complicated sequence of operations and rename it as a relation with totally different names. The reasons to rename a relation can be several, such as –
- We may want to save the result of a relational algebra expression as a relation in order that we are able to use it later.
- Furthermore, We may want to join a relationship with itself, furthermore, in such type of case, it becomes too confusing to specify that one in all the tables we are talking regarding, in that case, we rename one of the tables & perform SQL server join operations on them.
About Author
