Subqueries and SQL View
Section 1
SQL Subqueries
Learning Objectives:
In this lesson, you will learn how to use the SQL subquery to write complex queries and explain the correlated subquery concept.
By the end of this lesson, learners will be able to:
Describe subqueries.
Demonstrate how the subqueries and the correlated subquery depend on the outer query.
Prerequisite
Note: We will utilize a classicmodels database for demonstrations and examples in this presentation.
Table of Contents
Subqueries
Subqueries vs. JOINs
HAVING Subqueries
Correlated Subqueries
Subqueries with DML
Subqueries - Where Clause
Subqueries - Having Clause
Subqueries - From Clause
Subqueries
Subqueries are SQL SELECT statements nested within other SQL statements. In other words, a subquery is a query within (embedded) a query — also referred to as an inner-query and INNER-SELECT. We can create subqueries, such as select, insert, update and delete, within your SQL statements.
Subqueries can be used in the FROM clause, the WHERE clause, or the HAVING clause; they make SQL more dynamic.
The embedded query “container” query is known as the outer query.
SQL supports three types of subqueries: scalar subquery, row subquery, and table subquery.
Scalar subqueries only return a single row and a single column. They can be used with comparison operators =, <, <=, >, or >=.
Row subqueries only return a single row but can have more than one column. They can be used with IN and Not IN operators.
Table subqueries can return multiple rows, as well as columns.
Example:
Subqueries vs. JOINs
Subqueries and JOINs are both used to combine data from different tables into a single result. They share many similarities and differences. However, it is recommended to use a JOINs over a subquery for several reasons:
For performance issues, when it comes to getting data from multiple tables, it is strongly recommended to use JOINs instead of subqueries. Subqueries should only be used for this task for with good reason.
Subqueries should only be used as a fallback solution when you cannot use a JOIN operation.
By using JOINs, you can maximize the calculation burden on the database instead of on multiple queries using one join query. This means that you can make better use of the database’s abilities to search, filter, sort, etc.
However, the disadvantage of using JOINs is that they are not as easy to read as subqueries. Subqueries are more dynamic.
Subqueries - WHERE Clause
You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, EXISTS, ALL, etc.
The power of using a subquery with the WHERE clause is that you do not have to hard-code values within the comparisons. You can rely on the subquery results.
The WHERE clause is used to specify the conditions to filter records. The rows will be filtered according to the comparison between the results of the subquery and the mentioned column.
Syntax: Subquery in WHERE Clause:
Example 1: - Subqueries - WHERE Clause
Example 2: Subqueries - WHERE Clause
Example 3: Subqueries - WHERE Clause
Example 4: Subqueries - WHERE Clause
Subqueries - HAVING Clause
You can use subqueries with the HAVING clause to filter out groups of records. Just as the WHERE clause is used to filter rows of records, the HAVING clause is used to filter groups. Because of this, it becomes very useful in filtering on aggregate values such as averages, summations, and counts.
Syntax : Subquery in HAVING Clause
Example 1: Subqueries - HAVING Clause
Example 2: Subqueries - HAVING Clause
Subqueries - FROM Clause
Like all subqueries, those used in the FROM clause to create a derived table are enclosed by parenthesis. A table alias is used to select specific fields out of the subquery and is mandatory because all tables in the FROM clause must have a name in order to reference its results.
Syntax:
Subqueries with the FROM Clause are used to specify the source from which data has to be fetched. In the first case, the result of the subquery (inner query) will act as the source. In the second case, data will be fetched from table_name_1.
Syntax – Subquery in From Clause:
Example 2: Subqueries - HAVING and FROM Clause
Correlated Subqueries
Each subquery used in the prior examples can stand alone when run. SQL also allows us to construct subqueries, which reference table(s) in the outer query. A correlated subquery depends on the outer query.
This following query returns products with buyingprices > the average price in their product line.
In contrast to stand-alone subqueries, which are executed just once, correlated subqueries must execute once for every row processed in the outer query. This can lead to poor performance.
Hands-On Lab
Complete the GLAB - 304.7.1 - Subqueries. You can find this Lab on Canvas under the Assignment section. If you have any technical questions while performing the lab activity, ask your instructors for assistance.
Subqueries - Data Manipulation Language
We can use subqueries with INSERT, UPDATE, and DELETE statements.
Example 1- Subquery with UPDATE
The query below will update the status to pending for USA and JAPAN customers. We will utilize the subquery with WHERE clause.
Example 2- Subquery with UPDATE
The query below will update the status to backorder for those products that have stock of less than 40 items.
Example 3 - Subquery with UPDATE
Suppose you have to update the phone extensions of the employees who work at the office in San Francisco. The following query adds the number “1” to the phone extensions of employees who work at the office in San Francisco.
How it works.
First, the EXISTS operator in the WHERE clause gets only employees who work at the San Francisco office.
Second, the CONCAT() function concatenates the phone extension with the number ‘001.’
Practice Assignment
Complete the following practice assignments:
Note: Use your office hours to complete this assignment. If you have any technical questions while performing the practice assignment activity, ask your instructors for assistance.
Knowledge Check
What is a subquery in SQL?
What is a correlated subquery?
What is the difference between subqueries and SQL JOINs?
Which keyword is dependent upon the existence of the result of a query? a. EXISTS b. NOT EXISTS c. NOT NULL d. WHERE
Which of the following is not true about SQL subqueries? a. They are nested queries. b. They are executed first. c. Only one subquery can be nested. d. None of the above.
Learning Objectives
By the end of this lesson, learners will be able to:
Explain why we need SQL.
Explain View in SQL.
Utilize View in SQL.
Section 2
View
Outline
A VIEW in SQL.
Syntax: View.
Rename a View in SQL.
View Advantages.
A VIEW in SQL
An SQL VIEW is not a physical table, but rather, it is a virtual table formed by a query that joins one or more tables. When you create a view, you are essentially creating a query and giving it a name. As a result, a view is beneficial for enclosing an often-used complex query. The SQL View is essentially a saved query in the database.
Syntax: A VIEW in SQL
The CREATE VIEW CREATE VIEW statement creates a new view in the database. Here is the basic syntax of the statement:
Note: By default, the CREATE VIEW statement creates a view in the current/selected database. If you want to explicitly create a view in a given database, you can qualify the view name with the database name.
Example 1: A VIEW in SQL
Now, when you need to get customer payment data, query it from the VIEW by executing the following simple statement.
If you want to get customer payment data, you would normally construct a JOIN statement as follows:
Result:
Result: In View section
Rename a VIEW in SQL
To rename a VIEW, we can use the Rename Statement. The basic syntax is:
You can change the name of the VIEW from customer_payment to customer_info by using the following statement:
Remember: Since VIEWS are not physical tables, updating a View actually updates the underlying table.
VIEW Advantages
A VIEW can be very useful in some cases:
It avoids repetitions. If we have a query that is used frequently, we can just store it as a view instead of formulating and running it over and over again.
We can hide business logic or complexity by using VIEWs.
We can grant permission to users through a VIEW that contains specific data that the users are authorized to see.
We can also encapsulate sensitive queries in a VIEW and limit exposure, thus increasing security.
A VIEW provides a consistent layer; even the columns of underlying table changes.
A VIEW can be used as a backup for a table when migrating.
Knowledge Check
What is a View in SQL?
When do you usually use Views?
References
https://www.techonthenet.com/mariadb/exists.php#:~:text=The MariaDB EXISTS condition is,%2C UPDATE%2C or DELETE statement.
Questions?
Last updated