SQLite IN and NOT IN operators

Last update on August 19 2022 21:51:19 (UTC/GMT +8 hours)

Introduction

The IN and NOT IN operators take a single scalar operand on the left and a vector operand on the right formed by an explicit list of zero or more scalars or by a single subquery. When the right operand of an IN or NOT IN operator is a subquery, the subquery must have a single result column. When the right operand is an empty set, the result of IN is false and the result of NOT IN is true, regardless of the left operand and even if the left operand is NULL. The result of an IN or NOT IN operator is determined by the following matrix:

Left operand is NULL	Right operand contains NULL	Right operand is an empty set	Left operand found within right operand	Result of IN operator	Result of NOT IN operator
no	no	no	no	false	true
does not matter	no	yes	no	false	true
no	does not matter	no	yes	true	false
no	yes	no	no	NULL	NULL
yes	does not matter	no	does not matter	NULL	NULL

Syntax:

SELECT [column_name... | expression ]
FROM [table_name]
WHERE [NOT] IN (valu_1,value_2.....value_n);

SQLite Version: 3.8

Parameters

Name	Description
column_name	Name of the column of the table.
expression	Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of an SQLite query that compare values against other values or perform arithmetic calculations.
table_name	Name of the table.
value_1,value_2...	Values specified within the parentheses.

Example

To know whether the search value is present within the specified range, the following SQLite statement can be used :

SELECT 10 IN(10, 20, 30);

Here is the result.

10 IN(10, 20, 30)
-----------------
1

SELECT 4 IN(10, 20, 30);

Here is the result.

4 IN(10, 20, 30)
----------------
0

Example: SQLite IN operator with text value

The checking value of IN operator can also be a string or word or sentence. These values can also be checked within a set of values separated by commas and retrieve the rows containing these values.

Sample table: agents

To get data of all columns from the 'agents' table with the following condition -

1. 'working_area' for the 'agents' are any of 'London' or 'Mumbai' or 'Chennai',

the following SQLite statement can be used :

SELECT agent_code, agent_name, working_area 
FROM agents 
WHERE working_area IN ('London', 'Mumbai', 'Chennai');

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

AGENT_CODE  AGENT_NAME                                WORKING_AREA
----------  ----------------------------------------  ------------
A010        Santakumar                                Chennai
A002        Mukesh                                    Mumbai
A006        McDen                                     London

This statement can also be used like bellow :

SELECT agent_code, agent_name, working_area 
FROM agents  
WHERE working_area='London' 
OR working_area='Mumbai'
OR working_area='Chennai';

Relational Algebra Expression:

Relational Algebra Tree:

Here is the result.

AGENT_CODE  AGENT_NAME                                WORKING_AREA
----------  ----------------------------------------  ------------
A010        Santakumar                                Chennai
A002        Mukesh                                    Mumbai
A006        McDen                                     London

Example: SQLite IN operator with numeric value

In the following, we have discussed how a numeric value can be searched within a list of supplied value using IN operator in a select statement.

Sample table: agents

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' for the agents will be any of .13, .14 and .12,

the following SQLite statement can be used:

SELECT agent_code,agent_name,working_area FROM agents 
WHERE commission IN (.13,.14,.12);

Here is the result.

AGENT_CODE  AGENT_NAME                                WORKING_AREA
----------  ----------------------------------------  -------------------------------------
A003        Alex                                      London
A008        Alford                                    New York
A010        Santakumar                                Chennai
A012        Lucida                                    San Jose
A005        Anderson                                  Brisban
A001        Subbarao                                  Bangalore

Example: SQLite NOT IN operator

Sample table: agents

To get data of all columns from the 'agents' table with the following condition -

1. 'commission' for the agents will be none of .13, .14, .12,

the following SQLite statement can be used:

SELECT agent_code,agent_name,working_area
FROM agents 
WHERE commission NOT IN (.13,.14,.12);

Here is the result.

AGENT_CODE  AGENT_NAME  WORKING_AREA
----------  ----------  ------------
A007        Ramasundar  Bangalore
A011        Ravi Kumar  Bangalore
A002        Mukesh      Mumbai
A006        McDen       London
A004        Ivan        Torento
A009        Benjamin    Hampshair

Previous: BETWEEN Operator
Next: EXISTS Operator