SQLite Where Clause

SQLite WHERE Clause

In SQLite WHERE clause is used to impose restrictions on SELECT statement by defining one or more conditions to get required data from tables in database.

SQLite Where clause is used to limit the number of rows returned and to indicate a relationship used to join two tables together.

SQLite Where Clause Syntax

Following is the syntax of using SQLite where clause with select statement is as follows:

SELECT result

FROM table-list

WHERE expr;

In above sqlite select with where clause syntax we defined some of properties those are

result - It may be no. of column or expression that you want as result.

table-list - It may be list of table from which you want result.

expr - It is one or more conditions to retrieve the result.

SQLite Where Clause Example

For illustrating use of SQLite Where clause with SELECT statement, let’s get create one table called dept_master as follows.

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');

Same way create emp_master table and insert some records like as shown below.

CREATE TABLE emp_master

(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,

first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER references dept_master);

INSERT INTO emp_master

values (1,'Honey','Patel',10100,1),

(2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,12);

Let’s look at the simple example of fetching data of employees whose salary is greater than 10000 using SQLite where clause with select statement.

sqlite> SELECT first_name FROM emp_master WHERE salary > 10000;

When we run above sqlite select with where clause query we get result like as shown below.

first_name

----------

Honey

Shweta

Vinay

In SQLite we have number of relational operators that can be used with WHERE clause. Following table shows relational operators that can be used with WHERE clause in sqlite.

Operator	Description
a=b	a is equal to b
a != b	a is not equal to b
a < b	a is less than b
a > b	a is greater than b
a <= b	a is less than or equal to b
a >= b	a is greater than or equal to b
a IN (b, c)	a is equal to either b or c
a NOT IN (b, c)	a is equal to neither b nor c

SQLite Where Clause with Select Statement

Following is the example of using SQLite WHERE clause with SELECT statement.

sqlite> SELECT first_name,salary from emp_master WHERE emp_id = 4;

first_name  salary

----------  ----------

Jagruti     9500

Above query return result of employee whose emp_id is equal to 4. Now we will see another SQLite Where clause example.

sqlite> SELECT * FROM dept_master where dept_id IN (SELECT dept_id from emp_master);

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Control

Above SQLite query shows all the departments which is having employees. 

SQLite Where Clause with Not In

Here we will see another example of using SQLite where clause. In our emp_master table we have one employee whose dept_id 12 but there is no dept_id as 12 in dept_master. So we can get that employee by using NOT IN operator with where clause as follows.

sqlite> SELECT * FROM emp_master where dept_id NOT IN (SELECT dept_id from dept_master);

emp_id      first_name  last_name   salary      dept_id

----------  ----------  ----------  ----------  ----------

4           Jagruti     Viras       9500        12

This is how we can use SQLite Where clause in our database operations to get required data from tables based on our requirements.