Skip to content Skip to main navigation Skip to footer

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:
Subqueries in SQL

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 Example

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.
Subquery and 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.
Subquery and Null - No data found

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *