Here we will learn sqlite not condition with example and how to use SQLite not with null, sqlite not with between, sqlite not like with examples.
In SQLite NOT operator or condition is used to get the records that are not satisfying the defined conditions in sqlite select statement.
Generally, the SQLite Not condition (negate condition) is used along with NULL, IN, BETWEEN, LIKE in SELECT, INSERT, UPDATE, or DELETE statement.
Following is the Syntax of SQLite NOT operator to get records which are not existing or matching with defined values.
Expression NOT IN (value1, value2,.... value_n);
In the above SQLite NOT operator, we added NOT with IN Clause that means it returns the records which are not matching with the values defined. In our SQLite Not operator syntax, we defined some properties that are
We will see how to use SQLite Not with the IN clause to check column values with the subquery result set in the select statement.
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 NOT operator with IN to check column value with subquery in the select statement.
SELECT * from emp_master
WHERE dept_id
NOT IN (SELECT dept_id from dept_master
WHERE dept_name='Sales');
If you observe the above query we are getting employee details whose department id not matching with the “Sales” department. Now run the above query that will give a result like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
3 Vinay Jariwala 35100 3
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
8 Khyati Shah 50000 3
If you observe the above result it returned employees of all departments except the Sales department.
Following is the example of using SQLite NOT condition with LIKE in Select statement to get the employee details whose first name does not start with 'S'.
SELECT * from emp_master
WHERE first_name NOT LIKE 's%'
If you observe the above query we are trying to get employee details whose first_name not starts with 'S'. If we run the above query we will get a result like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Following is the example of using SQLite NOT with Between in Select statement to get employee details whose employee id not between 3 and 6.
SELECT * from emp_master
WHERE emp_id NOT BETWEEN 3 AND 6
If you observe the above query we are trying to get employee details whose emp_id not between 3 and 6. If we run the above query we will get a result like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
Following is the example of using SQLite NOT with Exists in Select statement to get employee details whose employees do not exist in the “Quality Control” department.
SELECT * from emp_master
WHERE dept_id
NOT EXISTS (SELECT dept_id from dept_master
WHERE dept_name='Quality Control');
If you observe above query we are trying to get employee details whose department not exists in “Quality Control”. If we run the above query we will get a result like as shown below.
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
4 Jagruti Viras 9500 2
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
This is how we can use SQLite NOT operator with IN, Exists, Between, NULL, etc to get required records from the table.