SQLite IN Operator

  Total Views : 47
  Comments : 0
 
  Keywords : SQLite in condition with example, SQLite in condition with subquery example, SQLite in operator with example, SQLit in operator to check given value matching with subquery values example

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.

SQLite IN Operator

In SQLite IN operator is used to determine whether the given value matching with subquery returned result set values or list of given values.

Syntax of SQLite IN Operator

Following is the syntax of SQLite IN operator to check whether the given column value matching with any of 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 list of values (val1, val2, etc.) or not.

 

expression: A column value to test.

 

value1, value2, ... value_n: The values to test against expression.

 

subquery: The expression value will text against result set of subquery.

SQLite IN Operator Example

We will see how to use SQLite IN operator with select statement example for that first create vegetables table and insert some data by running 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 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 vegetables table where the color is either ‘green’ or ‘orange’. If we run above query we will get result like as shown below.

 

name

----------

peas

carrot

cucumber

SQLite IN Operator with SubQuery

In above example, we used SQLite IN operator with string literals now we will see how to use sub query with SQLite IN operator. 

 

For this example, we need 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 dept_master table execute 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 following queries to create and insert some data in 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'101001),

(2,'Shweta','Jariwala', 193002),

(3,'Vinay','Jariwala', 351003),

(4,'Jagruti','Viras', 95002),

(5,'Shweta','Rana',120003),

(6,'sonal','Menpara', 130001),

(7,'Yamini','Patel', 100002),

(8,'Khyati','Shah', 5000003);

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 sub query in select statement.

 

SELECT * from emp_master

WHERE dept_id

IN (SELECT dept_id from dept_master

WHERE dept_name= 'Admin');

If you observe above query we used SQLite IN operator to check if dept_id value exists in the result set of sub query or not. Now run above SQLite query we will get 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 sub query based on our requirements.

 
 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User