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:

Select checkNumber, amount from payments
where amount > (Select avg(amount) from payments);

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:

SELECT column_name(s)
FROM table_name_1
WHERE column_name comparison_operator { =, NOT IN, IN, <, >, etc }
(SELECT column_name(s) from table_name_2);

Example 1: - Subqueries - WHERE Clause

Select checkNumber, amount from payments
where amount > 32431.65;

Select checkNumber, amount from payments
where amount > (select avg(amount) from payments);

Example 2: Subqueries - WHERE Clause

SELECT p.productCode, p.productName ,p.buyPrice FROM products p
WHERE p.buyPrice > (SELECT AVG(pp.buyPrice) FROM products pp)

Example 3: Subqueries - WHERE Clause

SELECT lastName, firstName FROM employees WHERE officeCode IN (1,2,3,2200,3001)

SELECT lastName, firstName, officeCode FROM employees
WHERE officeCode IN
(SELECT officeCode FROM offices WHERE country = 'USA');

Example 4: Subqueries - WHERE Clause

SELECT c.customerNumber, c.contactFirstName, checkNumber, amount, max(amount) FROMpayments p INNER JOIN customers c
on p.customerNumber = c.customerNumber
WHERE amount = (SELECT max(amount) FROM payments);

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

SELECT column_name(s)
FROM table_name_1
WHERE condition
GROUP BY column_name(s)
HAVING Aggregate_function(column_name) expression_operator{ =, <, > }
(SELECT column_name(s) from table_name_2);

Example 1: Subqueries - HAVING Clause

SELECT firstName, AVG(VacationHours) AS AverageVacationHours
FROM employees GROUP BY employeeNumber
HAVING AVG(VacationHours)  >  (SELECT AVG(VacationHours)   FROM   employees);

Example 2: Subqueries - HAVING Clause

SELECT pp.customerNumber, pp.checkNumber, pp.amount, c.customerName, AVG(pp.amount) FROM payments pp INNER JOIN customers c USING (customerNumber)
GROUP BY pp.customerNumber
HAVING AVG(pp.amount) > ( SELECT avg(p.amount) AS avgamount FROM payments p);

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:

SELECT *  FROM            AS
(subquery)
table_name_alias

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:

SELECT MAX(items), MIN(items),(AVG(items)) FROM (
SELECT     orderNumber, COUNT(orderNumber) AS items
FROM orderdetails GROUP BY orderNumber
)  AS lineitems;

Example 2: Subqueries - HAVING and FROM Clause

SELECT orderNumber, SUM(quantityOrdered * priceEach) GR
     FROM orderdetails
     GROUP BY orderNumber
     HAVING GR >= ( SELECT 0.9 * MAX(GR) FROM
( SELECT orderNumber,
SUM(quantityOrdered * priceEach) GR FROM orderdetails GROUP BY orderNumber) as gross);

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.

SELECT productname, buyprice FROM products p1 WHERE buyprice > ( SELECT AVG(buyprice) FROM products WHERE productline = p1.productline);

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.

UPDATE orders SET STATUS = 'pending' WHERE customerNumber IN ( SELECT c.customerNumber FROM customers c WHERE country IN ('USA' , 'JAPAN'))

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.

UPDATE orders SET status='Backorder' WHERE status='In Process' AND orderNumber IN ( SELECT DISTINCT od.orderNumber FROM orderDetails od JOIN products p USING(productCode) WHERE p.quantityInStock < 40);

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.

UPDATE employees SET extension = CONCAT(extension, '001') WHERE EXISTS (SELECT 1 FROM offices WHERE city = 'San Francisco' AND offices.officeCode = employees.officeCode);

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

  1. 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

  1. What is a subquery in SQL?

  2. What is a correlated subquery?

  3. What is the difference between subqueries and SQL JOINs?

  4. Which keyword is dependent upon the existence of the result of a query? a. EXISTS b. NOT EXISTS c. NOT NULL d. WHERE

  5. 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:

CREATE VIEW database_name.view_name [(column_list)] AS Select-Statement-Queries;

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:

CREATE VIEW customer_payment AS SELECT c.customerName, concat(contactfirstName, " ", contactLastName), p.amount, p.paymentDate FROM customers c INNER JOIN payments p ON c.customerNumber = p.customerNumber ORDER BY p.paymentDate;

SELECT * FROM customer_payment;

Result:

  • Result: In View section

Rename a VIEW in SQL

To rename a VIEW, we can use the Rename Statement. The basic syntax is:

RENAME Table <table-name> to new-table-name;

You can change the name of the VIEW from customer_payment to customer_info by using the following statement:

Rename table customer_payment to customer_info;

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

Questions?

PreviousSQL JOIN TablesNextSubprograms, Stored Procedures and Store functions

Last updated