SQL WITH AS Statement

Introduction to SQL WITH AS Statement

SQL AS statement helps us to specify an alias for table or column name. Alias is the alternative name that can be assigned to any of the objects inside the SQL query statement that includes the names of the tables and columns that help in accessing and referring those objects with an alternative and small word that is an alias which makes it easy for specifying. We have to careful while using aliases only about the name that we use for specifying the alias. The name of the alias should be such that it is meaningful and relevant to the object to which it is being assigned. The scope of the usage of the alias is only limited up to the query statement in which it is used.

Syntax:

name_of_table_or_column AS name_of_alias

where name_of_table_or_column is the column of the table name to which we have to assign the alternative name and name_of_alias is the alternative or temporary name with which the table of the column to which it is assigned can be referred further in the query statement. We can use the aliases for columns as well as for tables. We will study in a described way each of the cases further.

Examples of SQL WITH As Statement

Below are the usage of SQL With As Statement:

Example #1 – Using AS statement for Columns

Aliases are the alternative names that can be assigned to the values being retrieved from the query statement by specifying different columns and combinations of the columns. Aliases can also be assigned to the table names. The advantage of using the aliases is that shorter names can be assigned when the column names are large when multiple tables used in the query using different joins have same-named columns the aliases of the table can help to determine the column of which table should be retrieved or applied the condition when the aggregated functions and other combinations of the column values operations are performed the retrieved value can be assigned a name instead of the whole expression performed which makes the resultant records read in meaningful and easier way for analysis.

Let us create one table named educba_learning using the following query statement:

Query:

CREATE TABLE `educba_learning` (
`topic_id` INT(11) NOT NULL IDENTITY,
`subject` VARCHAR(100) DEFAULT NULL,
`sessions` INT(5) DEFAULT '0',
`expert_name` VARCHAR(100) DEFAULT NULL,
`charges` DECIMAL(7,2) DEFAULT '0.00')

Let us insert some records in the educba_learning table:

INSERT INTO `educba_learning` (`topic_id`, `subject`, `sessions`, `expert_name`, `charges`) VALUES
(1, 'SQL', '750', 'Payal Udhani', 75000),
(2, 'MySQL', '700', 'Siddharth Udhani', 35000),
(3, 'PostgreSQL', '600', 'Sonam Udhani', 45000),
(4, 'Hadoop', '980', 'Heena Udhani', 65000);

Output:

Let us firstly retrieve all the records of the table by simply using the following query statement that retrieves all the columns and rows of the table:

Query:

SELECT * FROM educba_learning;

Output:

Now, suppose, we want to retrieve the records such that the results must contain the name of the subject and the charges per session. This can be calculated by simply dividing the charges column by sessions column. Our query statement will be as follows:

Query:

SELECT subject, charges/sessions FROM educba_learning;

Output:

Instead of charges/sessions if we have to retrieve the column with some other name say “Charges Per Session” and of the subject as “Subject” then aliases can be used as follows:

Query:

SELECT SUBJECT AS "Subject", charges/sessions AS "Charges Per Session" FROM educba_learning;

Output:

Example #2 – Using AS Statement for Tables

When multiple tables are present in your database and there occurs a scenario where you have to retrieve the data from the tables that involve more than one tables and those tables may or may not have columns whose names are same then aliases are used to recognize all the tables involved in the query statement by assigning an alias to each of the tables in the query statement. Again the advantages of using an alias for table names are that if the names of the tables are very big then a short alias name can be used further in the same query once an alias is assigned to that table name.

One of the important usages of using aliases for tables is that when multiple tables have columns that have the same name and those tables are used in the same query then to identify the column that is being retrieved or applied restriction on can be specified by simply specifying the alias instead of the table name. Let us create one more table named educba_writers which has the following columns:

Query:

CREATE TABLE `educba_writers` (
`id` INT(11) NOT NULL,
`expert_name` VARCHAR(10) NOT NULL,
`rate` DECIMAL(5,2) DEFAULT NULL,
`joining_date_time` DATETIME DEFAULT NULL
);

Output:

Query:

INSERT INTO `educba_writers` (`id`, `expert_name`, `rate`, `joining_date_time`) VALUES
(1, 'Payal', '750.00', '2020-05-28 16:02:34'),
(2, 'Vyankatesh', '700.00', NULL),
(3, 'Omprakash', '600.00', '2020-05-28 20:32:50'),
(4, 'Parineeta', '980.00', NULL);

select * from educba_writers;

Output:

Now, we want to write a query that will retrieve the subject, session, expert name, and rate of expert that is writer, for this we will have to apply the join between educba_learning and educba_writers table. We will use aliases for the table as both the tables have the column name expert_name same and on this, we have to apply the join. Our query statement will be as follows:

Query:

SELECT
learn.subject,
learn.sessions,
learn.expert_name,
writer.rate
FROM
educba_learning learn
JOIN educba_writers writer
ON learn.expert_name = writer.expert_name ;

The execution of the above query statement where learn and writer are aliases for the educba_learning and educba_writers tables respectively will give the following output:

Output:

Conclusion

The AS statement in SQL helps in the creation of alternative names for tables and columns. These alternative names are also called as aliases. Aliases make the reference to the columns and tables easier as they provide a facility to refer those objects by an alternative name for a temporary basis in the scope of the query statement. Aliases prove helpful when the name of the tables is too big or not that meaningful.

Recommended Articles

This is a guide to SQL WITH AS Statement. Here we discuss the Introduction of SQL WITH AS Statement and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. Top 5 Examples of SQL Server Substring
2. Overview of Triggers in SQL
3. Introduction to SQL Arithmetic Operators
4. ANY in SQL | Examples | Syntax