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.
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.
Following is the syntax of using SQLite where clause with select statement is as follows:
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.
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,
INSERT INTO dept_master(dept_name)
Same way create emp_master table and insert some records like as shown below.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
dept_id INTEGER references dept_master);
INSERT INTO emp_master
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.
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.
|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|
Following is the example of using SQLite WHERE clause with SELECT statement.
sqlite> SELECT first_name,salary from emp_master WHERE emp_id = 4;
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);
3 Quality Control
Above SQLite query shows all the departments which is having employees.
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.