Fundamentals of Subqueries in SQL

Objectives

This lesson covers the following objectives:

• Define and explain the purpose of subqueries in SQL for retrieving data
• Construct and execute a single-row subquery in the WHERE clause
• Distinguish between single-row and multiple-row subqueries

Purpose

• Has a friend asked you to go to a movie, but before you could answer “yes” or “no”, you first had to check with your parents?
• Has someone asked you the answer to a math problem, but before you can give the answer, you had to do the problem yourself?
• Asking parents, or doing the math problem, are examples of subqueries.
• In SQL, subqueries enable us to find the information we need so that we can get the information we want.

What Are Subqueries in SQL

• Throughout this course, you have written queries to extract data from a database.
• What if you wanted to write a query, only to find out you didn’t have all the information you needed to construct it?
• You can solve this problem by nesting queries—placing one query inside the other query.
• The inner query is called a “subquery.”
• The subquery executes to find the information you don’t know.
• The outer query uses that information to find out what you need to know.
• Being able to combine two queries into one can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.
• A subquery is a SELECT statement that is embedded in a clause of another SELECT statement.
• A subquery executes once before the main query.
• The result of the subquery is used by the main or outer query.
• Subqueries can be placed in a number of SQL clauses, including the WHERE clause, the HAVING clause, and the FROM clause.
• The subquery syntax is:

Guidelines for Using Subqueries in SQL

• Guidelines:
  • The subquery is enclosed in parentheses.
  • The subquery is placed on the right side of the comparison condition.
  • The outer and inner queries can get data from different tables.
  • Only one ORDER BY clause can be used for a SELECT statement; if used, it must be the last clause in the outer query.
  • A subquery cannot have its own ORDER BY clause.
  • The only limit on the number of subqueries is the buffer size the query uses.

Two Types of Subqueries

• The two types of subqueries are:
  • Single-row subqueries that use single-row operators (>, =, >=, <, <>, <=) and return only one row from the inner query.
  • Multiple-row subqueries that use multiple-row operators (IN, ANY, ALL) and return more than one row from the inner query.

Subquery in SQL Example

• What if you wanted to find out the names of the employees that were hired after Peter Vargas?
• The first thing you need to know is the answer to the question, “When was Peter Vargas hired?”
• Once you know his hire date, then you can select those employees whose hire dates are after his.

SELECT first_name, last_name,
hire_date
FROM employees
WHERE hire_date >
(SELECT hire_date
FROM employees
WHERE last_name = 'Vargas');

Subquery and Null

• If a subquery returns a null value or no rows, the outer query takes the results of the subquery (null) and uses this result in its WHERE clause.
• The outer query will then return no rows, because comparing any value with a null always yields a null.

• Who works in the same department as Grant?
• Grant’s department_id is null, so the subquery returns NULL.
• The outer query then substitutes this value in the WHERE clause (WHERE department_id = NULL).
• The outer query returns no rows, because comparing anything with a null returns a null.

Terminology

Key terms used in this lesson included:

• Subquery
• Inner query
• Outer query
• Single-row subquery
• Multiple-row subquery

Summary of Subqueries in SQL

In this lesson, you should have learned how to:

• Define and explain the purpose of subqueries for retrieving data
• Construct and execute a single-row subquery in the WHERE clause
• Distinguish between single-row and multiple-row subqueries