Understanding AND, OR, and NOT Operators in SQL: A Comprehensive Guide

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax: AND

SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition1 AND condition2 AND condition3 ..., AND columnmN;

Syntax: OR

SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition1 OR condition2 OR condition3 ..., OR columnN;

Syntax: NOT

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

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example: AND Example

Code:

SELECT * FROM Employee
WHERE EmpDept = 'ADMIN' AND EmpName = 'Sofik'

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
8	Sofik	Kolkata	ADMIN

Example: OR Example

Code:

SELECT * FROM Employee
WHERE EmpDept = 'ADMIN' OR EmpName = 'Rubin'

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example: NOT Example

Code:

SELECT * FROM Employee
WHERE NOT EmpDept='IT'

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept	EmpId
3	Rohon	Kolkata	ITIS	3
4	Ronok	Kolkata	ITIS	4
5	Rubin	Kolkata	ITIS	5
6	Sorif	Kolkata	ADMIN	6
7	Soriful	Kolkata	ADMIN	7
8	Sofik	Kolkata	ADMIN	8

Example: Combining AND, OR and NOT

Code:

SELECT * FROM Employee
WHERE EmpDept='ADMIN' AND (EmpAddress='Kolkata' OR EmpName='Sofik')

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN