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.

CodingSight - SQL AS Statement

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.

How About an Example?

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

What to Watch Out for

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.

AS Statement usage

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.

Latest posts by Oleg Zhuk (see all)

Oleg Zhuk

Master's degree in Security of Information and Communication Systems. Researcher (information security), professional in the field of information security organization. Oleg is constantly studying new technologies and is happy to write articles on these topics for blogs, online publications, and thematic communities.

Leave a Reply

Your email address will not be published. Required fields are marked *