{"id":127,"date":"2016-03-23T09:51:32","date_gmt":"2016-03-23T09:51:32","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=127"},"modified":"2025-02-06T01:58:39","modified_gmt":"2025-02-06T08:58:39","slug":"sql-in","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-in\/","title":{"rendered":"SQL IN Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL IN<\/code> operator to check if a value is in a set of values.<\/p>\n\n\n\n

Introduction to SQL IN Operator #<\/a><\/h2>\n\n\n\n

The IN<\/code> is one of the logical operators<\/a> in SQL. The IN<\/code> operator returns true<\/code> if a value is in a set of values or false<\/code> otherwise.<\/p>\n\n\n\n

Here’s the syntax of the IN<\/code> operator:<\/p>\n\n\n

expression IN (value1, value2,...)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
expression = value1 OR expression = value2 OR ...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NOT IN<\/code> operator negates the result of the IN<\/code> operator. Here’s the syntax of the NOT IN<\/code> operator:<\/p>\n\n\n
expression NOT IN (value1, value2,...)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NOT IN<\/code> operator returns true<\/code> if the expression<\/code> does not equal any values in the list (value1, value2, …) or false<\/code> otherwise.<\/p>\n\n\n\n
Technically, you can rewrite the NOT IN<\/code> operator using the not equal to (!=<\/code> ) and AND<\/code> operators as follows:<\/p>\n\n\n
expression != value1 AND expression != value2 AND...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that if any value in the list (value1,value2,...)<\/code> is NULL<\/code>, the IN<\/code> operator returns no rows.<\/p>\n\n\n\n
Later, you’ll learn how to use the IN<\/code> and NOT IN<\/code> operator with subqueries<\/a>.<\/p>\n\n\n\n
SQL IN operator examples #<\/a><\/h2>\n\n\n\n
We will use the  employees<\/code> table in the sample database<\/a> to demonstrate the functionality of the IN<\/code> operator.<\/p>\n\n\n\n
\"employees_table\"<\/figure>\n\n\n\n
Using the IN operator with numeric data #<\/a><\/h3>\n\n\n\n
The following example uses the IN<\/code> operator to find employees with the job id is 8, 9, or 10:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  job_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  job_id IN<\/span> (8<\/span>, 9<\/span>, 10<\/span>)\nORDER<\/span> BY<\/span>\n  job_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 first_name | last_name | job_id\n------------+-----------+--------<\/span>\n Susan      | Mavris    |      8\n Bruce      | Ernst     |      9\n David      | Austin    |      9\n Alexander  | Hunold    |      9\n Diana      | Lorentz   |      9\n Valli      | Pataballa |      9\n Michael    | Hartstein |     10<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the NOT IN<\/code> operator to find employees with the job id is not 7<\/code>, 8<\/code>, or 9<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  job_id\nFROM<\/span>\n  employees\nWHERE<\/span>\n  job_id NOT<\/span> IN<\/span> (7<\/span>, 8<\/span>, 9<\/span>)\nORDER<\/span> BY<\/span>\n  job_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 first_name  |  last_name  | job_id\n-------------+-------------+--------<\/span>\n William     | Gietz       |      1\n Shelley     | Higgins     |      2\n Jennifer    | Whalen      |      3\n Steven      | King        |      4\n Neena       | Kochhar     |      5\n Lex         | De Haan     |      5\n Jose Manuel | Urman       |      6\n Luis        | Popp        |      6\n John        | Chen        |      6\n Ismael      | Sciarra     |      6\n Daniel      | Faviet      |      6\n Michael     | Hartstein   |     10\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using the IN operator with character data #<\/a><\/h3>\n\n\n\n
The following query uses the IN<\/code> operator to retrieve employees whose first name is either Steven<\/code>, Lex<\/code>, or Daniel<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name\nFROM<\/span>\n  employees\nWHERE<\/span>\n  first_name IN<\/span> ('Steven'<\/span>, 'Lex'<\/span>, 'Daniel'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name\n------------+-----------<\/span>\n Steven     | King\n Lex        | De Haan\n Daniel     | Faviet<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using the IN operator with date values #<\/a><\/h3>\n\n\n\n
The following statement uses the IN<\/code> operator to retrieve employees who joined the company on one of these dates: 1987-06-17<\/code> , 1994-08-16<\/code> , and 1997-09-30<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name, \n  hire_date\nFROM<\/span>\n  employees\nWHERE<\/span>\n  hire_date IN<\/span> ('1987-06-17'<\/span>, '1994-08-16'<\/span>, '1997-09-30'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name | hire_date\n------------+-----------+------------<\/span>\n Steven     | King      | 1987-06-17\n Daniel     | Faviet    | 1994-08-16\n Ismael     | Sciarra   | 1997-09-30<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using the IN operator with a function #<\/a><\/h3>\n\n\n\n
The following query uses the EXTRACT<\/code> function to extract month from the hire_date<\/code> column and the IN<\/code> operator to retrieve employees who joined the company in January<\/code>, February<\/code>, or March<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name, \n  hire_date,\n  EXTRACT<\/span>(MONTH<\/span> FROM<\/span> hire_date) joined_month\nFROM<\/span>\n  employees\nWHERE<\/span>\n  EXTRACT<\/span>(MONTH<\/span> FROM<\/span> hire_date) IN<\/span> (1<\/span>, 2<\/span>, 3<\/span>)\nORDER<\/span> BY<\/span> \n  joined_month;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 first_name  | last_name | hire_date  | joined_month\n-------------+-----------+------------+--------------<\/span>\n Lex         | De Haan   | 1993-01-13 |            1\n Alexander   | Hunold    | 1990-01-03 |            1\n Karen       | Partners  | 1997-01-05 |            1\n Charles     | Johnson   | 2000-01-04 |            1\n Michael     | Hartstein | 1996-02-17 |            2\n Sarah       | Bell      | 1996-02-04 |            2\n Valli       | Pataballa | 1998-02-05 |            2\n Diana       | Lorentz   | 1999-02-07 |            2\n Britney     | Everett   | 1997-03-03 |            3\n Jonathon    | Taylor    | 1998-03-24 |            3\n Jose Manuel | Urman     | 1998-03-07 |            3<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n
In practice, you often use the IN<\/code> and NOT IN<\/code> operators in the  WHERE<\/a><\/code> clause of the SELECT<\/a><\/code> statement to filter rows with a value in a set of values.<\/p>\n\n\n\n
Technically, you can substitute the IN<\/code> operator with the equal to(=<\/code> ) and OR<\/a><\/code> operators. In other words, you can rewrite the IN<\/code> operator using one or more OR<\/a><\/code> operators like this:<\/p>\n\n\n