SQL NOT Operator

Introduction to SQL NOT Operator

NOT operator in standard query language(SQL) is a logical operator that is used to filter records when used in conjunction with conditions mentioned in the WHERE clause. It returns only those rows or records which do not satisfy the mentioned condition. That is, it displays a row only if the condition is FALSE or NOT TRUE. We can use NOT in combination with other operators such as BETWEEN, OR, AND, etc. Do not worry, we will learn all these with the help of some practical problems. Without waiting any longer, let us begin with syntax and parameters used for writing queries with NOT operator.

Syntax and parameters:

The basic syntax used for using NOT OPERATOR in SQL queries is as follows :

SELECT column_name_1, column_name_2, ...
FROM table_name
WHERE NOT condition_expression;

The parameters used in the above-mentioned syntax are as follows:

• column_name_1, column_name_2, … : Columns or fields that have to be displayed in the final result set.
• table_name : The database table from which the said columns have to be fetched.
• condition_expression : Condition on the basis of which the records have to filtered. More specifically, the condition for which the rows must return FALSE to make it the final result set.

Having discussed the syntax and parameter used for writing SQL queries with NOT Operator, let us try a few examples based on it to understand the concept in detail.

Examples

In order to illustrate the usage of NOT operator in SQL, let us create a dummy table called “students”. As the name suggests, this table contains details of students such as id, name, society, major etc. We can use the following code snippet to create this table.

CREATE TABLE students
(
student_id character varying(50) NOT NULL,
student_name character varying(255) NOT NULL,
degree_major character varying(255) NOT NULL,
degree_year character varying(255),
society character varying(255)
);

Having created the students table, let us insert a few records in it to work with.

INSERT INTO students(
student_id, student_name, degree_major, degree_year, society)
VALUES ('1','Mohith K','Computer Science Engineering','IV','Dramatics'),
('2','Ayesha Khan','Electrical Engineering','I','Music'),
('3','Kylie Green','Computer Science Engineering','III','Choreography'),
('4','Alisha Rojer','Chemical Engineering','III','Music'),
('5','Andy Bernard','Geosciences','IV','Dramatics'),
('6','Ron Weasley','chemistry','II','Music'),
('7','Ross Geller','Paleontology',NULL,'Dramatics'),
('8','Harvey Ross','Chemistry','III','Music');
select * from students;

The data after successful insertion in the students table looks as follows:

Now we are all set to try a few examples based on NOT operator with the help of the students table.

Basic Functionality of NOT Operator

Lets us discuss Basic Functionality of NOT Operator with examples.

Example #1: Find student id and degree year of students who are not in IVth year.

Code:

SELECT student_id, degree_year FROM students
WHERE NOT degree_year = 'IV';

Instead of NOT operator, we can use ‘<>’ i.e NOT EQUAL TO operator for such cases. The above query will look something like this.

SELECT student_id, degree_year FROM students
WHERE degree_year <>'IV';

NOT with OR logical operator

Example #2: Find student id and degree year of students who are not from IVth or Ist year.

Code: 

SELECT student_id, degree_year FROM students
WHERE NOT (degree_year = 'IV' OR degree_year = 'I');

NOT with AND logical operator

Example #3: Find student id, name, major, year, and society of students who are not from IIIrd year and do not belong to music society.

Code:

SELECT student_id, student_name, degree_major, degree_year, society FROM students
WHERE NOT (society = 'Music' AND degree_year = 'III');

NOT with IN operator

Example #4: Find student id, name, and society of students who are not from music and dramatics society.

Code:

SELECT student_id, student_name, society FROM students
WHERE society NOT IN ('Music', 'Dramatics');

NOT with BETWEEN operator

Example #5: Find student id, name, year, and society of students whose ids are not between 2 and 5.

Code:

SELECT student_id,student_name, degree_year, society FROM students
WHERE student_id NOT BETWEEN '2' AND '5';

NOT with NULL VALUES

Example #6: Find student id, name, year, and society of students whose degree year values are not NULL.

Code: 

SELECT student_id,student_name, degree_year, society FROM students
WHERE degree_year is NOT NULL;

NOT with LIKE WILDCARD

Example #7: Find student id, name, year, and society of students whose name do not start with ‘A’.

 Code:

SELECT student_id,student_name, degree_year, society FROM students
WHERE student_name NOT LIKE 'A%';

NOT with EXISTS operator

Example #8: Find student id, name and year of students only when there exists no student in ‘Music’ society.

Code:

SELECT student_id, student_name, degree_year FROM students
WHERE NOT EXISTS (SELECT student_id FROM students WHERE society = 'Music');
select * from students;

Usage of NOT in UPDATE statements

Example #9: Update the value of degree year field to ‘IV’, for students who do not belong to music society.

Code: 

UPDATE students
SET degree_year = 'IV'
WHERE student_id NOT IN (SELECT student_id FROM students WHERE society = 'Music');

The column has been successfully updated, we can check this with the help of a SELECT query.

SELECT * FROM students;

Usage of NOT in DELETE statements

Example #10: Delete all records of IVth year students.

Code:

DELETE FROM students
WHERE NOT degree_year = 'IV';

The records have been successfully deleted, we can check this with the help of a SELECT query.

SELECT * FROM students;

Conclusion: SQL NOT Operator

NOT operator is used to filter records based on the negation of specified conditions in the WHERE or HAVING clause of a SQL query.

Recommended Articles

This is a guide to SQL NOT Operator. Here we discuss the definition, syntax, parameters, and Basic Functionality of NOT Operator with Examples and code implementation. You may also have a look at the following articles to learn more –

1. SQL Select Top
2. SQL Cluster
3. SQL Merge Two Tables
4. SQL DROP TRIGGER