SQLite: NOT Condition
This SQLite tutorial explains how to use the SQLite NOT condition with syntax and examples.
Description
The SQLite NOT Condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the NOT Condition in SQLite is:
NOT condition
Parameters or Arguments
- condition
- The condition to negate.
Note
- The SQLite NOT condition requires that the opposite of the condition must be met for the record to be included in the result set.
Example - Combine With IN condition
The SQLite NOT condition can be combined with the IN Condition.
For example:
SELECT * FROM employees WHERE department NOT IN ('Accounting', 'Finance', 'HR');
This SQLite NOT example would return all rows from the employees table where the department is not 'Accounting', 'Finance', or 'HR'. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
Example - Combine With IS NULL condition
The SQLite NOT condition can also be combined with the IS NULL Condition.
For example,
SELECT * FROM employees WHERE department IS NOT NULL;
This SQLite NOT example would return all records from the employees table where the department does not contain a NULL value.
Example - Combine With LIKE condition
The SQLite NOT condition can also be combined with the LIKE Condition.
For example:
SELECT last_name, first_name FROM employees WHERE last_name NOT LIKE 'A%';
By placing the SQLite NOT Operator in front of the LIKE condition, you are able to retrieve all employees whose last_name does not start with 'A'.
Example - Combine With BETWEEN condition
The SQLite NOT condition can also be combined with the BETWEEN Condition. Here is an example of how you would combine the NOT Operator with the BETWEEN Condition.
For example:
SELECT * FROM employees WHERE employee_id NOT BETWEEN 5 AND 10;
This SQLite NOT example would return all rows from the employees table where the employee_id is NOT between 5 and 10, inclusive. It would be equivalent to the following SELECT statement:
SELECT * FROM employees WHERE employee_id < 5 OR employee_id > 10;
Example - Combine With EXISTS condition
The SQLite NOT condition can also be combined with the EXISTS Condition.
For example,
SELECT * FROM employees WHERE NOT EXISTS (SELECT * FROM positions WHERE positions.position_id = employees.position_id);
This SQLite NOT example would return all records from the employees table where there are no records in the positions table for the given position_id.
Advertisements