EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial SQL NOT Operator

SQL NOT Operator

Priya Pedamkar
Article byPriya Pedamkar

Updated March 10, 2023

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

SQL NOT Operator 1

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';

Output:

SQL NOT Operator 2

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';

Output:

SQL NOT Operator 3

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');

Output:

SQL NOT Operator 4

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');

Output:

SQL NOT Operator 5

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');

Output:

SQL NOT Operator 6

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';

Output:

Example 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;

Output:

Example 6

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%';

Output:

Example 7

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;

Output:

Example 8

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;

Example 9

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;

Example 10

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

We hope that this EDUCBA information on “SQL NOT Operator” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL Select Top
  2. SQL Cluster
  3. SQL Merge Two Tables
  4. SQL DROP TRIGGER
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW