AppDividend
Latest Code Tutorials

SQL AND OR NOT Operator Example Tutorial

0

SQL AND OR NOT Operator Example Tutorial is today’s topic. The SQL AND, OR and NOT operators are used for filtering the records based on one or more conditions. The AND, OR operators are used with where clause. It can also be combined with the NOT operators. The AND OR operator is also known as Conjunctive operators.

SQL AND OR NOT Operator

AND, OR operator is also known as Conjunctive operators.

  1. The AND operator displays the record only if the conditions separated by AND operator results in True. All the conditions separated by 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 OR operator results in True.
  3. The NOT operator displays the record if the condition is false.

Let’s understand all the operators with proper syntaxes and examples.

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, the 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 condition. 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 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 that details of the employee were to be displayed whose name is Rohit or Suraj. As OR operator was used and we know that if either of the condition is true, it will display the result. As both of them 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

 

#EXPLANATION

As you can see from the above query that 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 Employee 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 Was displayed as in 1st condition his city was Jalandhar and in the Second condition which was associated with OR, Shivam 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 Employee has displayed whose city was not in Jalandhar and Allahabad.

Finally, SQL AND OR NOT Operator Example Tutorial is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.