{"id":384,"date":"2019-05-30T01:11:52","date_gmt":"2019-05-30T09:11:52","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=384"},"modified":"2020-04-11T06:59:24","modified_gmt":"2020-04-11T14:59:24","slug":"db2-subquery","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-subquery\/","title":{"rendered":"Db2 Subquery"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the Db2 subquery or subselect which is a SELECT<\/code> statement nested inside another statement such as SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, and DELETE<\/code>.<\/p>\n\n\n\n

Introduction to Db2 subquery<\/h2>\n\n\n\n

A subquery is a nested SQL statement that contains a SELECT<\/code> statement inside the WHERE<\/a><\/code> or HAVING<\/a><\/code> clause of another SQL statement. A subquery is called a subselect.<\/p>\n\n\n\n

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<\/code>:<\/p>\n\n\n

SELECT<\/span> \n\ttitle, rating, publisher_id \nFROM<\/span> \n\tbooks\nWHERE<\/span> \n\tpublisher_id IN<\/span> (\n\t\tSELECT<\/span> \n\t\t\tpublisher_id \n\t\tFROM<\/span> \n\t\t\tpublishers\n\t\tWHERE<\/span> \n\t\t\tname<\/span> LIKE<\/span> '%Oxford%'<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
In this example, here is the subquery:<\/p>\n\n\n
SELECT<\/span> \n\tpublisher_id \nFROM<\/span> \n\tpublishers\nWHERE<\/span> \n\tname<\/span> LIKE<\/span> '%Oxford%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The subquery is always enclosed in parentheses.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To better understand the result of the outer query, you can imagine that Db2 goes through the following process:<\/p>\n\n\n\n
1) DB2 first executes the subquery to get a list of publisher id:<\/p>\n\n\n
SELECT<\/span>\n   publisher_id\nFROM<\/span>\n   publishers\nWHERE<\/span>\n   name<\/span> LIKE<\/span> '%Oxford%'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
PUBLISHER_ID\n-------------<\/span>\n148\n149\n150\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) DB2 then uses this list for the search condition of the outer query:<\/p>\n\n\n
SELECT<\/span> \n\ttitle, \n\trating, \n\tpublisher_id \nFROM<\/span> \n\tbooks\nWHERE<\/span> \n\tpublisher_id IN<\/span> (148<\/span>,149<\/span>,150<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
Nested Subqueries<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Db2 Subquery types<\/h2>\n\n\n\n
Db2 allows you to use a subquery in the following:<\/p>\n\n\n\n