Updated May 15, 2023
Introduction to SQL WITH AS Statement
SQL AS statement helps us to specify an alias for a 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 terms 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 to specify. We have to be careful while using aliases only about the name that we use for specifying the alias.
Syntax:
name_of_table_or_column AS name_of_aliaswhere name_of_table_or_column is the name of the table’s column to which the temporary name must be assigned, and name_of_alias is a different name that may be used in the query statement to refer to the column table to which it is assigned. 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
A table’s name and the values returned by a query statement can both have aliases, which are names. The benefits of assigning aliases might be numerous. First, to make the query easier to read and comprehend, aliases can be used to give shorter names to columns or tables with long or complicated names.
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 first 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 the 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, there occurs a scenario where you have to retrieve the data from the tables that involve more than one table. Those tables may or may not have columns whose names are the same; then, aliases are used to recognize all the tables involved in the query statement by assigning a handle to each table in the query statement. Again the advantages of using an alias for table names are that if the terms 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.
When many tables have columns with the same name, one of the primary uses of table aliases is to identify the column that is being fetched or applied restrictions by simply specifying the alias rather than 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 create a query that will obtain the written expert’s name, session, subject, and rating; for this, we must 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 the 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 create alternative names for tables and columns. Aliases make the reference to the columns and tables easier as they provide a facility to refer to those objects by an alternative name for a temporary basis in the scope of the query statement. Aliases prove helpful when the tables’ names are too big or meaningless.
Recommended Articles
We hope that this EDUCBA information on “SQL WITH AS Statement” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
