SQL NOT Operator – Syntax and Examples

SQL NOT Operator

The SQL NOT operator is used to filter records by reversing the result of a condition. When combined with other operators, such as AND, OR, LIKE, or IN, NOT negates the specified condition. This allows you to retrieve records that do not meet a certain criterion.

In this tutorial, we will go through NOT Operator in SQL, its syntax, and how to use this operator in forming conditions in SQL statements, with well detailed examples.

Syntax of SQL NOT Operator

The basic syntax of the SQL NOT operator in a WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from the table.
• FROM: Specifies the table from which to retrieve data.
• WHERE: Introduces the condition used to filter the data.
• NOT: Reverses the result of the specified condition, returning only rows that do not meet it.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the NOT operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality.

Setup for Examples: Creating the Database and Table

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a table named students:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert some sample records to use in our examples:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

---

Examples: Using NOT Operator in Queries

Now, let’s explore different scenarios of using the NOT operator with this table.

---

1. Using NOT with a Simple Condition

To select students who are not in the 9th grade:

SELECT * FROM students
WHERE NOT grade = '9th';

This query returns rows where the grade is not “9th”, displaying records for Alice, Charlie, and David.

---

2. Using NOT with Numeric Comparison

To select students who are not 15 years old:

SELECT * FROM students
WHERE NOT age = 15;

This query returns students who are not 15 years old, displaying records for Alice, Charlie, and David.

---

3. Using NOT with Multiple Conditions

To select students who are not 14 years old and not in the 8th grade:

SELECT * FROM students
WHERE NOT (age = 14 AND grade = '8th');

This query returns students who do not meet both conditions (14 years old and in 8th grade). It displays records for Bob, David, and Eva.

---

4. Using NOT with LIKE for Pattern Matching

To select students whose names do not contain the letter “a”:

SELECT * FROM students
WHERE name NOT LIKE '%a%';

This query returns students whose names do not contain the letter “a”, displaying only the record for Bob.

---

5. Using NOT with IN for Exclusion

To select students who are not in the 9th or 10th grade:

SELECT * FROM students
WHERE grade NOT IN ('9th', '10th');

This query returns students who are neither in the 9th grade nor in the 10th grade, displaying records for Alice and Charlie.

FAQs for SQL NOT

1. What does the SQL NOT operator do?

The NOT operator reverses the result of a specified condition. It returns rows where the condition is false, allowing you to filter out specific values.

2. Can I use NOT with other operators in SQL?

Yes, you can use NOT with operators like AND, OR, LIKE, and IN to create complex conditions that exclude certain records.

3. How does NOT work with LIKE?

Using NOT LIKE allows you to exclude rows based on pattern matching. For example, name NOT LIKE 'A%' excludes names that start with “A”.

4. Can I use NOT with IN for excluding multiple values?

Yes, NOT IN is used to exclude multiple specific values from a column. For example, WHERE grade NOT IN ('9th', '10th') excludes those grades.

5. Can I combine NOT with multiple conditions?

Yes, you can combine NOT with multiple conditions using parentheses, such as WHERE NOT (age = 14 AND grade = '8th'), to reverse the result of a compound condition.

Related

SQL OR Operator

SQL AND Operator