{"id":196,"date":"2019-03-27T03:17:52","date_gmt":"2019-03-27T11:17:52","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=196"},"modified":"2020-04-11T06:59:54","modified_gmt":"2020-04-11T14:59:54","slug":"db2-in","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-in\/","title":{"rendered":"Db2 IN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 IN<\/code> to compare a value with a set of values.<\/p>\n\n\n\n

Introduction to Db2 IN operator<\/h2>\n\n\n\n

The Db2 IN<\/code> operator is a logical operator that compares a value with a set of values:<\/p>\n\n\n

expression IN (v1, v2, v3, ...)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IN<\/code> operator returns true if the value of the expression<\/code> matches one of the value in the list v1<\/code>, v2<\/code>, v3<\/code>… Otherwise, it returns false.<\/p>\n\n\n\n
The value list can be literal values as shown in the syntax above or a result set of a query. In this case, we called the query that supplies value to IN operator is a subquery<\/a>. The subquery must return a single column or you will get an error.<\/p>\n\n\n\n
The following illustrates the syntax of the IN<\/code> operator with a subquery.<\/p>\n\n\n
expression IN (subquery)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The IN<\/code> operator is equivalent to multiple OR<\/code> operators:<\/p>\n\n\n
expression = v1 OR \nexpression = v2 OR \nexpression = v3 OR\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using IN<\/code> operator makes the code cleaner and easier to understand.<\/p>\n\n\n\n
To negate the IN<\/code> operator, you use the NOT<\/code> operator as follows:<\/p>\n\n\n
 expression NOT IN (v1, v2, v3, ...)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It is equivalent to the following syntax:<\/p>\n\n\n
expression <> v1 AND\nexpression <> v2 AND \nexpression <> v3 AND\n...\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 IN operator examples<\/h2>\n\n\n\n
We will use the books<\/code> table from the sample database<\/a> to demonstrates the IN<\/code> operator.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using Db2 IN operator with a list of numeric values<\/h3>\n\n\n\n
This example uses the IN<\/code> operator to find the books whose publisher id is in the list of 100, 103, and 105:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    publisher_id\nFROM<\/span>    \n    books\nWHERE<\/span> \n    publisher_id IN<\/span> (100<\/span>, 103<\/span>, 105<\/span>)\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
The query above is equivalent to the following query that uses multiple OR<\/code> operators:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    publisher_id\nFROM<\/span>    \n    books\nWHERE<\/span> \n    publisher_id = 100<\/span>\n        OR<\/span> publisher_id = 103<\/span>\n        OR<\/span> publisher_id = 105<\/span>\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using Db2 IN with a subquery<\/h3>\n\n\n\n
The following query uses the LIKE<\/code> operator to find publisher ids of the publisher whose name starts with 'Addison Wesley'<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    publisher_id\nFROM<\/span>    \n    publishers\nWHERE<\/span> name<\/span> LIKE<\/span> 'Addison Wesley%'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns three rows:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
You can use this result set to supply to the IN<\/code> operator to find books whose publisher name starts with 'Addison Wesley'<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    publisher_id\nFROM<\/span>    \n    books\nWHERE<\/span> publisher_id IN<\/span>\n(\n    SELECT<\/span>    \n        publisher_id\n    FROM<\/span>    \n        publishers\n    WHERE<\/span> name<\/span> LIKE<\/span> 'Addison Wesley%'<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
3) Using Db2 NOT IN operator example<\/h3>\n\n\n\n
The following example uses the NOT IN<\/code> operator to find books whose publisher id is not in the list (100,103,105)<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    publisher_id\nFROM<\/span>    \n    books\nWHERE<\/span> \n    publisher_id NOT<\/span> IN<\/span> (100<\/span>, 103<\/span>, 105<\/span>)\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is as follows:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
It is equivalent to the following query that uses multiple AND<\/code> operators:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    publisher_id\nFROM<\/span>    \n    books\nWHERE<\/span> publisher_id <> 100<\/span>\n        AND<\/span> publisher_id <> 103<\/span>\n        AND<\/span> publisher_id <> 105<\/span>\nORDER<\/span> BY<\/span> \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Db2 IN<\/code> operator to compare a value with a list of values.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
You often use the IN<\/code> operator in the WHERE<\/a><\/code> clause of the SELECT<\/code><\/a>, DELETE<\/code>, and UPDATE<\/code> statements.<\/p>\n\n\n\n