SQL AND OR NOT Operator: The Complete Guide

AND, OR operator is also known as Conjunctive operators. Let’s understand all the operators with proper syntaxes and examples.

SQL AND OR NOT Operator

SQL AND, OR, and NOT operators filter the records based on one or more conditions. The AND, OR operators are used with the where clause. It can also be combined with the NOT operators. The AND / OR operator is also known as the Conjunctive operator.

1. The AND operator displays the record only if the conditions separated by AND operator result in True. All the conditions separated by the AND operator should be true; otherwise, it will not display all the records.
2. The OR operator displays the records if either of the conditions separated by the OR operator results in True.
3. The NOT operator displays the record if the condition is False.

Consider a Table: (Employee)

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

AND Operator

See the following syntax.

Select * from table_name 
where condition1 AND condition2 ……….;

Parameters

1. Table_name: Name of the Table.
2. condition1 AND condition2 ……….: Condition1 and Condition2 and so on.

See the following example.

Select * from Employee 
where Emp_id>200 AND Emp_name like ‘S%’;

See the output.

Emp_id	Emp_name	City	State	Salary
201	Shivam	Jalandhar	Punjab	20000
401	Suraj	Kolkata	West Bengal	60000

Explanation

As you can see from the query, condition1 was to display the details of an employee whose Emp_id was more significant than 200 and the second condition was to check whose name starts with S.

We have used Select, Where statements and like operator. For the second condition, refer to SQL WILDCARD CHARACTERS.

As only 2 records satisfy all the conditions. Hence, they are displayed.

Example: 2

Select * from Employee
Where Emp_id > 300 AND SALARY < 70000;

See the following output.

Emp_id	Emp_name	City	State	Salary
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000

Explanation

As you can see from the query, the condition1 was to display the details of an employee whose Emp_id was greater than 300, and the second condition was to check whose salary was less than 70000.

Hence, the above two records were displayed.

OR Operator

See the following syntax.

Select * from table_name 
Where condition1 OR condition2 ……….;

Parameters

1. Table_name: Name of the Table.
2. condition1 OR condition2 ……….: Condition1 and Condition2 and so on.

See the following example.

Select * from Employee 
Where Emp_name=’Rohit’ OR Emp_name=’Suraj’;

See the following output.

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
401	Suraj	Kolkata	West Bengal	60000

Explanation

As you can see from the above query, the details of the employee were to be displayed whose name is Rohit or Suraj. As the OR operator was used, we know that if either condition is true, it will display the result as both were true.

Hence, the records are displayed. See the following example.

Select * from Employee 
where Emp_id >200 OR Emp_name=’Rohit’;

See the output.

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Example

As you can see from the above query, the details of an employee were to be declared whose Emp_id was greater than 200 or whose name was Rohit.

As both of the conditions are true. Hence, the results are displayed.

NOT Operator

See the following syntax.

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

Parameters

1. Table_name: Name of the Table.
2. WHERE NOT condition: Condition which is not True.

Example

Select * from Employee 
where NOT City=’Patna’;

See the output.

Emp_id	Emp_name	City	State	Salary
201	Shivam	Jalandhar	Punjab	20000
301	Karan	Allahabad	Uttar Pradesh	40000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

 

Explanation

As you can see from the above query, the records of employees have displayed whose City was not in Patna.

Combining AND, OR, and NOT

See the following example.

Select * from Employee 
where City=’Jalandhar’ 
AND (Emp_name=’Shivam’ OR Emp_name=’Karan’);

See the following output.

Emp_id	Emp_name	City	State	Salary
201	Shivam	Jalandhar	Punjab	20000

Explanation

Here in the above query, details of Shivam Were displayed as in 1st condition his City was Jalandhar. In the second condition, which was associated with OR, Shivam’s name was present in Jalandhar.

Hence, his name was displayed.

Example

See the following query.

Select * from Employee 
where NOT City=’Jalandhar’ 
AND NOT City=’Allahabad’;

See the output.

Emp_id	Emp_name	City	State	Salary
101	Rohit	Patna	Bihar	30000
401	Suraj	Kolkata	West Bengal	60000
501	Akash	Vizag	Andhra Pradesh	70000

Explanation

As in the above query, details of the Employee have displayed whose City was not in Jalandhar and Allahabad.

That’s it for this tutorial.