SQLite Where Clause

Here we will learn sqlite where clause with example and how to use sqlite select with where clause to get data from tables with example.

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 the properties those are

result - It may be no. of column or expression that you want as a result.
table-list - It may be the list of a table from which you want results.
expr - It is one or more conditions to retrieve the result.

SQLite Where Clause Example

For illustrating the 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');

The 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 will get a result as shown below.

first_name

----------

Honey

Shweta

Vinay

In SQLite, we have a number of relational operators that can be used with the WHERE clause. The 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 the SQLite WHERE clause with the SELECT statement.

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

first_name  salary
----------  ----------
Jagruti     9500

The above query will return the result of an 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

The above SQLite query shows all the departments that are 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