{"id":159,"date":"2019-03-26T19:13:16","date_gmt":"2019-03-27T03:13:16","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=159"},"modified":"2020-04-11T07:00:05","modified_gmt":"2020-04-11T15:00:05","slug":"db2-where","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-where\/","title":{"rendered":"Db2 WHERE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 WHERE<\/code> clause to specify the search condition for rows returned by a query.<\/p>\n\n\n\n

Introduction to Db2 WHERE clause<\/h2>\n\n\n\n

The WHERE<\/code> clause is an optional clause of the SELECT<\/a><\/code> statement. The WHERE<\/code> clause specifies the search condition for the rows returned by the SELECT<\/code> statement.<\/p>\n\n\n\n

The following shows the syntax of the WHERE<\/code> clause in the SELECT<\/code> statement:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nWHERE<\/span>\n    search_condition\nORDER<\/span> BY<\/span>\n    sort_expression;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the search_condition<\/code> like a filter that defines a condition for the returned rows. The rows that cause the search_condition<\/code> evaluate to true will be included in the result set.<\/p>\n\n\n\n
The search_condition<\/code> may consist of one or many logical expressions that evaluate to true, false, or unknown. The logical expressions in the WHERE<\/code> clause are also known as predicates.<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
Besides the SELECT<\/code> statement, the WHERE<\/code> clause are used in the UPDATE<\/code> or DELETE<\/code> statement to specify rows to be updated or deleted.<\/p>\n\n\n\n
Db2 WHERE clause examples<\/h2>\n\n\n\n
We’re going to use the books<\/code> table from the sample database<\/a> to demonstrate the WHERE<\/code> clause:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using simple equality operator in Db2 WHERE clause example<\/h3>\n\n\n\n
This query uses the WHERE<\/code> clause to find books whose ratings are 4:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages, \n    rating, \n    published_date\nFROM<\/span>    \n    books\nWHERE<\/span> \n    rating = 4<\/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 are the output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
2) Using comparison operators in the Db2 WHERE clause example<\/h3>\n\n\n\n
The following query uses the WHERE<\/code> clause to return books whose ratings are greater than 4.7 and less than or equal to 5:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages, \n    rating, \n    published_date\nFROM<\/span>    \n    books\nWHERE<\/span> rating > 4.7<\/span>\n        AND<\/span> rating <= 5<\/span>\nORDER<\/span> BY<\/span> \n    rating, \n    title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the result set:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
3) Using Db2 WHERE clause to find rows with the value between two values<\/h3>\n\n\n\n
The following statement uses the BETWEEN<\/a><\/code> operator in the WHERE<\/code> clause to find books whose published dates are between Jan 01, 2018<\/code> and December 31, 2018<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages, \n    rating, \n    published_date\nFROM<\/span>    \n    books\nWHERE<\/span> \n    published_date BETWEEN<\/span> '2018-01-01'<\/span> AND<\/span> '2018-12-31'<\/span>\nORDER<\/span> BY<\/span> \n    published_date;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
4) Using Db2 WHERE clause to find rows that have a value in a list of values<\/h3>\n\n\n\n
The following example uses the IN<\/a><\/code> operator in the WHERE<\/code> clause to find books whose rating is 4 or 5<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages, \n    rating \nFROM<\/span>    \n    books\nWHERE<\/span> rating IN<\/span> (4<\/span>, 5<\/span>)\nORDER<\/span> BY<\/span> \n    rating;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
5) Using Db2 WHERE clause to find rows whose values contain a string<\/h3>\n\n\n\n
This example uses the LIKE<\/a><\/code> operator in the WHERE<\/code> clause to find books whose title contains the string 'SQL'<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages, \n    rating, \n    published_date\nFROM<\/span>    \n    books\nWHERE<\/span> title LIKE<\/span> '%SQL%'<\/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
\"\"<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 WHERE<\/code> clause to specify the search condition for rows returned by a query.<\/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
The WHERE<\/code> clause appears after the FROM<\/code> clause and before the ORDER BY<\/a><\/code> clause. When evaluating the SELECT<\/code> statement, Db2 evaluates the clauses in the following sequence: FROM<\/code>, WHERE<\/code>, SELECT<\/code>, and ORDER BY<\/code>.<\/p>\n\n\n\n