Here we will learn SQLite IN operator with example and how to use SQLite IN operator with subquery values to check if column value matching or not with example.
In SQLite IN operator is used to determine whether the given value matching with subquery returned result set values or list of given values.
Following is the syntax of SQLite IN operator to check whether the given column value matching with any of the value in the list of values or subquery values.
expression IN (val1, val2,.... val_n);
OR
expression IN (subquery);
The above sqlite in operator syntax will check whether the given expression value matching with the list of values (val1, val2, etc.) or not.
We will see how to use SQLite IN operator with select statement example for that first create a vegetable table and insert some data by running the following queries.
CREATE TABLE vegetable(
name TEXT,
color TEXT);
INSERT INTO vegetable
values ('peas', 'green'),
('carrot', 'orange'),
('cucumber', 'green'),
('tomato', 'red');
Let’s look at records of vegetable table by running following query.
sqlite> SELECT * FROM vegetable
name color
---------- ----------
peas green
carrot orange
cucumber green
tomato red
Now write SQLite IN operator query like as shown below to check whether color matching with any of the given list values or not.
SELECT name
FROM vegetable
WHERE color IN ('green','orange');
If you observe above SQLite IN condition example, we are trying to get rows from the vegetable table where the color is either ‘green’ or ‘orange’. If we run the above query we will get a result like as shown below.
name
----------
peas
carrot
cucumber
In the above example, we used SQLite IN operator with string literals now we will see how to use subquery with SQLite IN operator.
For this example, we need to create two tables dept_master and emp_master and need to insert some data for that use following queries.
To create and insert some data in the dept_master table execute the following query.
CREATE TABLE dept_master
(dept_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_name TEXT);
INSERT INTO dept_master(dept_name)
VALUES('Admin'),
('Sales'),
('Quality Control');
('Marketing');
Once we create and insert data execute following query to see the data of dept_master table.
sqlite> SELECT * FROM dep_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Control
4 Marketing
Same way execute the following queries to create and insert some data in the emp_master table.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel', 10100, 1),
(2,'Shweta','Jariwala', 19300, 2),
(3,'Vinay','Jariwala', 35100, 3),
(4,'Jagruti','Viras', 9500, 2),
(5,'Shweta','Rana',12000, 3),
(6,'sonal','Menpara', 13000, 1),
(7,'Yamini','Patel', 10000, 2),
(8,'Khyati','Shah', 500000, 3);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Now write SQLite query like as shown following to use IN operator with subquery in select statement.
SELECT * from emp_master
WHERE dept_id
IN (SELECT dept_id from dept_master
WHERE dept_name= 'Admin');
If you observe the above query we used SQLite IN operator to check if dept_id value exists in the result set of subquery or not. Now run the above SQLite query we will get a result like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
6 Sonal Menpara 13000 1
Here we got all the employees of Admin Department.
This is how we can use SQLite IN operator to check if column value matching with given list of values or result set of subquery based on our requirements.