SQL AS: Usage, Examples, and How it Can Benefit You Best

What is the AS statement in SQL? The AS keyword in SQL is used to create a temporary alias for a table or column. This alias can be used instead of the name in the SELECT statement.

The SQL language has three different ways of naming things: identifiers, table aliases, and column aliases. These are just nicknames we give things so they’re easier to talk about and work with. A good example of this would be your email address. It may be anything you want it to be. However, many people choose to use their first initial and last name instead of full names, so that they’re easier to find.

TLDR!

The AS statement is a very important part of SQL. It can be used to assign table aliases or column aliases, or it can create temporary tables for storing intermediate results in your query.

Syntax of SQL WITH AS Statement

The AS statement in SQL is a part of the FROM clause. Also, it is used to assign aliases to columns and tables within the SELECT, WHERE, JOIN, and GROUP BY clauses.

It is a placeholder that can define an alias for the object being created. This means that instead of naming the table, column, or another object you create under its own name, you can use the AS statement:

CREATE TABLE 'my_table' AS (id INT NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) NOT NULL);

The examples of the “AS” statements are as follows:

• SELECT AS Product, Name FROM Products;
• CREATE VIEW Orders AS SELECT * FROM Order Details.

Here’s an example of using AS to specify an alias:

SELECT CONCAT_WS(' ', CustomerInfList_FirstName, CustomerInf_LastName) AS First_LastName
FROM Customer_InformationListForSale

In SQL, we use the AS keyword to create a temporary alias for an existing table. This can be helpful when working with long or complex names that would otherwise clutter up queries and code.

Important: AS aliases are only visible within the current scope of the statement where they’re created. Once you leave this scope, they will no longer exist.

How to Use the AS Statement

To use an alias, enter:

SELECT FROM TableA AS t1 JOIN TableB AS t2 ON (t1.ID = t2.ID) WHERE …

This code will be extracted from two tables that have the same ID field, and it will join them based on this column.

Example 1: Renaming a Column with AS

Consider a table Employees with the columns EmployeeID, FirstName, LastName, Email, and Salary. If you want to make your column names more readable, you can use the AS keyword to rename them in your query:

SELECT FirstName AS 'First Name', LastName AS 'Last Name', Email AS 'Email Address'
FROM Employees;

The above SQL statement selects the FirstName, LastName, and Email columns from the “Employees” table and renames them to ‘First Name’, ‘Last Name’, and ‘Email Address’ in the output.

Example 2: Renaming a Table with AS

You can also rename tables with AS, which is especially useful in JOIN operations:

SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees AS e
INNER JOIN Departments AS d ON e.DepartmentID = d.DepartmentID;

In this example, Employees is renamed to e and Departments is renamed to d. This makes the SQL shorter and more readable, especially when you’re using the table names multiple times in your queries.

Example 3: Using AS with Functions

The AS keyword can also be used to name the result of a function:

SELECT COUNT(*) AS 'Total Employees'
FROM Employees;

This SQL statement counts the number of rows in the “Employees” table, and the result will be named ‘Total Employees’.

How Aliases Can be Useful

Table aliases are a fundamental concept in SQL programming. They allow you to specify which table and column of data you request information from, making it easier to see what you are working with. There are some queries that you simply can’t run without aliases in SQL.

A table alias is a placeholder for the name of a table. We often use table aliases when writing SQL queries. They are especially useful when a query contains multiple references to the same table.

Errors may sometimes happen because columns and tables have the same name. Using a table alias helps avoid ambiguity and keeps your queries clear and concise.

Which Types of Queries you Can’t Use without Aliases

A SQL query is a structured request for information that can be posed to a relational database management system. Queries are made up of different clauses that may or may not include tables, views, subqueries, and functions. There are three types of queries that cannot be executed without using an alias – subqueries, self-joins, and joins.

A self-join is a query that combines data from two or more tables in the same database and retrieves information based on an identical column name in both. This type of join is called a “self-join” because it uses one table as its own source of data. As both of these tables have the same names, it’s impossible to avoid aliases (Oracle Database 10g Express Edition):

SELECT a.emp_id AS "Emp_ID",a.emp_name AS "Employee Name",
b.emp_id AS "Supervisor ID",b.emp_name AS "Supervisor Name"
FROM employee a, employee b
WHERE a.emp_supv = b.emp_id;

Summary

The SQL AS statement is a way to assign aliases. It’s also used as an assignment operator in some languages and can be combined with JOINs or other operators to produce the desired result set.

We can use the alias name as a table name in any command instead of having to type out the fully qualified name. This is helpful when you want to refer to a column more than once, such as for sorting or filtering purposes.