DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 Subquery

Db2 Subquery

Summary: in this tutorial, you will learn about the Db2 subquery or subselect which is a SELECT statement nested inside another statement such as SELECT, INSERT, UPDATE, and DELETE.

Introduction to Db2 subquery

A subquery is a nested SQL statement that contains a SELECT statement inside the WHERE or HAVING clause of another SQL statement. A subquery is called a subselect.

The subquery allows you to form a search condition based on the data in another table. For example, you can find all books by publishers whose name contains the keyword Oxford:

SELECT title, rating, publisher_id FROM books WHERE publisher_id IN ( SELECT publisher_id FROM publishers WHERE name LIKE '%Oxford%' );
Db2 Subquery Example

In this example, here is the subquery:

SELECT publisher_id FROM publishers WHERE name LIKE '%Oxford%';

The subquery is always enclosed in parentheses.

A subquery is also known as an inner query or inner select while the query that contains the subquery is known as an outer query or outer select.

To better understand the result of the outer query, you can imagine that Db2 goes through the following process:

1) DB2 first executes the subquery to get a list of publisher id:

SELECT publisher_id FROM publishers WHERE name LIKE '%Oxford%';

Here is the output:

PUBLISHER_ID ------------- 148 149 150

2) DB2 then uses this list for the search condition of the outer query:

SELECT title, rating, publisher_id FROM books WHERE publisher_id IN (148,149,150);

By using the subquery, you are able to combine steps together. The subquery removes the step of selecting the publisher id list and plugging them into the outer select. On top of it, the result of the query is automatically adjusted whenever the publisher data changes.

Nested Subqueries

Db2 allows you to nest a subquery within another subquery. The relationship between the nested subquery and subquery is the same as the relationship between the subquery and outer query. The maximum level of nesting in Db2 is 15.

Db2 Subquery types

Db2 allows you to use a subquery in the following:

  • in the place of expression in the SELECT clause
  • in the FROM clause
  • within the IN or NOT IN operator in the WHERE clause.
  • within the ANY or ALL operator in the WHERE clause
  • within the EXISTS or NOT EXISTS operator in the WHERE clause.

1) Using a subquery in place of an expression example

When a subquery returns a single value, you can place it in place of an expression e.g., in the select list of the SELECT clause.

This example uses a subquery to find the average number of pages of all books in the books table:

SELECT title, total_pages, (SELECT ROUND(AVG(total_pages),0) FROM books ) avg_pages FROM books ORDER BY title;
Db2 Subquery in SELECT clause example

2) Using a subquery with IN operator example

You often use a subquery with the IN operator. In this case, the subquery returns zero or multiple values. The outer query makes use of these values as a filter.

The following statement returns all books from the authors whose first name is Tim.

SELECT title FROM books b INNER JOIN book_authors A ON A.book_id = b.book_id WHERE author_id IN ( SELECT author_id FROM authors WHERE first_name = 'Tim' );
Db2 Subquery with the IN clause example

In this example, the subquery returns a list of author ids which are used in the outer query to find their books.

3) Using subquery with ANY operator example

The following illustrates the syntax of a subquery used with the ANY operator:

expression comparison_operator ANY (subquery)

Suppose, the subquery returns a list of value v1, v2, …. The ANY operator returns true if one of the following comparison pair evaluates to true:

(expression, v1) (expression, v2) (expression, ...)

Note that the comparison operators are =, >, >=, <, <=, and <>.

The following statement uses a query in the ANY operator to find books which have more than five authors.

SELECT book_id, title, rating FROM books WHERE book_id = ANY( SELECT book_id FROM book_authors GROUP BY book_id HAVING COUNT(author_id) > 5 );
Db2 Subquery with ANY operator example

4) Using a subquery with ALL operator example

The syntax of using a subquery with the ALL operator is similar to the syntax of using the subquery with the ANY operator:

expression comparison_operator ALL (subquery)

The ALL operator returns true if all comparison pairs return to TRUE.

This statement uses the ALL operator to find the books whose ratings are greater than the average rating of all books by publishers:

SELECT title, rating FROM books WHERE rating > ALL( SELECT AVG(rating) FROM books GROUP BY publisher_id );
Db2 Subquery with ALL operator example

5) Using a subquery with EXISTS operator example

Here is the syntax of using a subquery with EXISTS and NOT EXISTS operator:

WHERE [NOT] EXISTS (subquery)

The EXISTS operator returns true only if the subquery returns a non-empty result set. The NOT operator negates the EXISTS operator.

The following query finds the authors who have books published in 2019:

SELECT first_name, last_name FROM authors a INNER JOIN book_authors b ON b.author_id = a.author_id WHERE EXISTS ( SELECT book_id FROM books WHERE YEAR(published_date) = 2018 AND book_id = b.book_id );
Db2 Subquery with the EXISTS operator example

In this tutorial, you have learned about the Db2 subquery and how to use various types of subqueries to query data.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 HAVING
Next Db2 UNION

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.