SQL NOT Operator

Introduction

In this chapter, we will learn how to use the NOT operator in SQL. The NOT operator is used to negate a condition in a SQL query. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the NOT operator effectively.

What is the NOT Operator?

The NOT operator is used to reverse the result of a condition in a SQL statement. When used with a condition, it returns true if the condition is false and false if the condition is true. It is commonly used in the WHERE clause to exclude rows that meet certain criteria.

Syntax for NOT

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• condition: The condition to be negated.

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE NOT department_id = 1;

This command retrieves the first_name, last_name, and email columns from the employees table where the department_id is not 1.

Step-by-Step Example

1. Create Sample Tables

First, we will create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Using the NOT Operator

To retrieve employees who are not in department 1:

SELECT first_name, last_name, email
FROM employees
WHERE NOT department_id = 1;

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

4. Combining NOT with Other Conditions

You can combine the NOT operator with other conditions using AND and OR operators.

Example

To retrieve employees who are not in department 1 and have a salary greater than 50000:

SELECT first_name, last_name, email
FROM employees
WHERE NOT department_id = 1 AND salary > 50000;

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com

Using NOT with Other Operators

The NOT operator can be used with other SQL operators like IN, BETWEEN, LIKE, etc., to create more complex queries.

Example with IN

To retrieve employees who are not in departments 1 and 3:

SELECT first_name, last_name, email
FROM employees
WHERE department_id NOT IN (1, 3);

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Priya	Sharma	priya.sharma@example.com

Example with BETWEEN

To retrieve employees whose salary is not between 50000 and 60000:

SELECT first_name, last_name, email
FROM employees
WHERE salary NOT BETWEEN 50000 AND 60000;

Output

first_name	last_name	email
Priya	Sharma	priya.sharma@example.com

Example with LIKE

To retrieve employees whose first_name does not start with 'R':

SELECT first_name, last_name, email
FROM employees
WHERE first_name NOT LIKE 'R%';

Output

first_name	last_name	email
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

Conclusion

The NOT operator is a fundamental tool for negating conditions in SQL queries. This chapter covered the basic syntax, combining NOT with other conditions, and using the NOT operator with other SQL operators. Understanding how to use the NOT operator effectively will enhance your ability to query and analyze your database data.