SQLite Not Operator

  Total Views : 39
  Comments : 0
 
  Keywords : SQLite not condition with example, SQLite not equal query with example, SQLite not null query in select statement with example, SQLite not operator with IN clause example

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.

SQLite Not Operator

In SQLite NOT operator or condition is used to get the records which 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.

Syntax of SQLite Not Operator

Following is the Syntax of SQLite NOT operator to get records which are not exists or matching with defined values.

 

Expression NOT IN (value1, value2,.... value_n);

In 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 those are

 

expression - The column value to test.

 

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

SQLite Not IN with SubQuery Example

We will see how to use SQLite Not with IN clause to check column values with subquery result set in select statement. 

 

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

 

SELECT from emp_master

WHERE dept_id

NOT IN (SELECT dept_id from dept_master

WHERE dept_name='Sales');

If you observe above query we are getting employee details whose department id not matching with “Sales” department. Now run above query that will give 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 above result it returned employees of all department except Sales department.

SQLite NOT with LIKE Example

Following is the example of using SQLite NOT condition with LIKE in Select statement to get employee details who first name not starts with 'S'.

 

SELECT from emp_master

WHERE first_name NOT LIKE 's%'

If you observe above query we are trying to get employee details whose first_name not starts with 'S'. If we run above query we will get 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

SQLite NOT with Between Example

Following is the example of using SQLite NOT with Between in Select statement to get employee details who employee id not between 3 and 6.

 

SELECT from emp_master

WHERE emp_id NOT BETWEEN 3 AND 6

If you observe above query we are trying to get employee details whose emp_id not between 3 and 6. If we run above query we will get 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

SQLite NOT with Exists Example

Following is the example of using SQLite NOT with Exists in Select statement to get  employee details who employees not exists in “Quality Control” departement.

 

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 above query we will get 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 table.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User