SQL AS Keyword

Introduction to AS in SQL

When temporarily naming columns or tables so they are later identified, the AS keyword is used.

The SQL statement chooses the Customers table's last_name column in this case. In the result set, the column name is changed to name.

SELECT last_name AS name:

This is the main part of the query where you specify the columns you want to retrieve from the "Customers" table. In this case, you are selecting the column named "last_name" from the table. Additionally, you are using the AS keyword to give this selected column an alias or a new name, which is "name". This means that the column will be referred to as "name" in the result set, rather than its original name "last_name".

SQL AS Alias Syntax

The SQL AS command has the following syntax:

Here,

• column_1, column_2,...column_n are the table columns
• alias_1, alias_2,...alias_n are the aliases of the table columns

Examples of AS in SQL

Assigning a Temporary Name to the Column of a Table

For example,

The first_name field of the Customers table is chosen in this SQL statement. However, the column name will change to the name in the result set.

SQL AS with More than One Column

Additionally, we can utilize aliases with multiple columns.

For instance,

Here, the SQL command selects customer_id as cid and first_name as a name.

This SQL query is written in the standard SQL language. SQL (Structured Query Language) interacts with databases, and queries like this are used to retrieve specific data from a database table.

• customer_id AS cid:
  This part of the query specifies that you want to retrieve the customer_id column from the Customers table, and you want to give it an alias or alternative name, cid. The alias is used to make the output more readable and user-friendly.
• first_name AS name:
  Similarly, this part of the query specifies that you want to retrieve the first_name column from the Customers table and give it an alias, name.

Assigning a Temporary Name to a Table

Additionally, you can rename tables using AS, which is particularly helpful for JOIN operations:

Employees and Departments are renamed to e and d, respectively, in this example. When you use the table names repeatedly in your queries, this helps make the SQL shorter and easier to read.

• FROM Employees AS e:
  This clause specifies the source table from which data will be retrieved. The table "Employees" is aliased as "e" to make it easier to reference in the query.
• INNER JOIN Departments AS d ON e.DepartmentID = d.DepartmentID:
  This is the join clause. It specifies that you want to combine data from the "Employees" table (aliased as "e") and the "Departments" table (aliased as "d") based on a common column, which is the "DepartmentID" column. An INNER JOIN returns only the rows where there is a match between the specified columns in both tables.

In summary, this SQL query retrieves the first name and last name of employees along with the corresponding department name for each employee. It achieves this by joining the "Employees" and "Departments" tables based on the common "DepartmentID" column. The result is a list of employees' names along with their respective department names.

SQL AS with Functions

When working with functions, it's common to create aliases using AS. For instance,

Here, the total_customers attribute reflects the value of the SQL command's count of all the rows.

A total_customers column will be present in the return set of this command.

It retrieves information from a database table called "Customers". Let's break down the code step by step:

COUNT(*):

This is an aggregate function in SQL. It counts the number of rows in a specified table. In this case, it counts all the rows in the "Customers" table.

AS total_customers:

The AS keyword is used to provide an alias for the result of the COUNT(*) function. The alias assigned here is "total_customers." An alias is like a nickname or a label that makes the result more human-readable.

FROM Customers:

This specifies the table from which the data is retrieved. In this case, the table is named "Customers".

Putting it all together, the code asks the database to count the total number of rows (customers) in the "Customers" table and provide the result with the label "total_customers".

So, if you were to execute this SQL query against a database, it would return a single value representing the total number of customers in the "Customers" table, and it would be labeled as "total_customers". This label makes it easier to understand the purpose of the result when used in applications or reports.

SQL AS with JOIN

When working with JOIN, we can use AS aliases with table names to keep our snippet clear and organized. For instance,

The SQL query, in this case, temporarily names the Customers table as C and the Orders table as O. It then picks the customer_id, first_name, and amount from C and O, respectively.

Columns for cid, name, and amount will be present in the result set of this command.

It retrieves information from two tables, Customers and Orders, using a JOIN operation. Let's break down the code step by step to understand what it does:

SELECT C.customer_id AS cid, C.first_name AS name, O.amount:

This line defines the columns that will be selected in the final result. The query is selecting three columns:

• C.customer_id is being aliased as cid.
• C.first_name is being aliased as a name.
• O.amount is selected as is.

FROM Customers AS C:

This line specifies the source table for the query. The table Customers is being aliased as C. This alias allows us to refer to the Customers table using the shorthand C throughout the rest of the query.

JOIN Orders AS O:

This line performs a JOIN operation with the Orders table, which is being aliased as O. This means that the query will combine rows from the Customers table with matching rows from the Orders table based on a specified condition.

The overall purpose of the query is to retrieve information about customers and their associated orders. By performing an inner join between the Customers and Orders tables, the query retrieves customer IDs, customer names, and the order amounts for each customer who has made orders. The result will contain rows where each row represents a customer who has placed an order, along with their customer ID, first name, and the amount of the order.

In summary, the query combines data from the Customers and Orders tables to create a result set that shows customer information alongside the amounts of their respective orders.