SQLite | Query Data | Compare Against a List of Values (IN Clause)
The IN clause tests whether a column matches any value in a list. This article explains how to use it.
For conditional queries, see the article on the WHERE clause.
Conditions with IN
Use IN with a value list to test for a match. The syntax is as follows.
SELECT column_name, ... FROM table_name WHERE column IN (value1, value2, ...);
A row is returned when the column matches any listed value. This is equivalent to combining equality comparisons with OR.
SELECT column_name, ... FROM table_name WHERE column = value1 OR column = value2 OR ...;
Use NOT IN to retrieve rows whose value is absent from the list.
SELECT column_name, ... FROM table_name WHERE column NOT IN (value1, value2, ...);
–
Create the following table for an example.
create table user (name text, old integer, address text);
sqlite> create table user (name text, old integer, address text);
sqlite>
Insert data into the table.
insert into user values ('devkuma', 39, 'Seoul');
insert into user values ('kimkc', 34, 'Busan');
insert into user values ('araikuma', 26, 'Paju');
insert into user values ('happykuma', 19, 'Paju');
insert into user values ('mykuma', 27, 'Daejeon');
insert into user values ('yourkuma', 28, 'Seongnam');
insert into user values ('raccoon', 31, 'Suwon');
sqlite> insert into user values ('devkuma', 39, 'Seoul');
sqlite> insert into user values ('kimkc', 34, 'Busan');
sqlite> insert into user values ('araikuma', 26, 'Paju');
sqlite> insert into user values ('happykuma', 19, 'Paju');
sqlite> insert into user values ('mykuma', 27, 'Daejeon');
sqlite> insert into user values ('yourkuma', 28, 'Seongnam');
sqlite> insert into user values ('raccoon', 31, 'Suwon');
sqlite>
A query without a condition returns the following rows.
select * from user;
sqlite> select * from user;
name old address
---------- ---------- ----------
devkuma 39 Seoul
kimkc 34 Busan
araikuma 26 Paju
happykuma 19 Paju
mykuma 27 Daejeon
yourkuma 28 Seongnam
raccoon 31 Suwon
sqlite>
Use IN to retrieve rows whose address is Paju, Seongnam, or Suwon.
select * from user where address in ('Paju', 'Seongnam', 'Suwon');
sqlite> select * from user where address in ('Paju', 'Seongnam', 'Suwon');
name old address
---------- ---------- ----------
araikuma 26 Paju
happykuma 19 Paju
yourkuma 28 Seongnam
raccoon 31 Suwon
sqlite>
Only matching rows are returned.
Comparing Against Values from Another Table
Instead of listing values directly, a subquery can obtain the comparison list from another table. The syntax is as follows.
SELECT column_name, ... FROM table_name WHERE column IN (SELECT column_name FROM table_name);
The subquery must return one column. A row is selected when its value matches one of the returned values.
Create the following table.
create table gyeonggi (id integer, region text);
sqlite> create table gyeonggi (id integer, region text);
sqlite>
Insert data into the table.
insert into gyeonggi values (1, 'Paju');
insert into gyeonggi values (2, 'Seongnam');
insert into gyeonggi values (3, 'Suwon');
insert into gyeonggi values (4, 'Gimpo');
sqlite> insert into gyeonggi values (1, 'Paju');
sqlite> insert into gyeonggi values (2, 'Seongnam');
sqlite> insert into gyeonggi values (3, 'Suwon');
sqlite> insert into gyeonggi values (4, 'Gimpo');
sqlite>
Query user for rows whose address matches a region value from gyeonggi.
select * from user where address in (select region from gyeonggi);
sqlite> select * from user where address in (select region from gyeonggi);
name old address
---------- ---------- ----------
araikuma 26 Paju
happykuma 19 Paju
yourkuma 28 Seongnam
raccoon 31 Suwon
sqlite>
Only matching rows are returned.