Table of contents
1.
Introduction
2.
AND & OR clause
3.
AND clause
3.1.
Truth Table
3.2.
Syntax
4.
OR clause
4.1.
Truth Table
4.2.
Syntax
5.
Combining AND & OR clause
5.1.
Easy
5.2.
Medium
5.3.
Hard
6.
Working of AND and OR clauses with different statements
6.1.
UPDATE statement 
6.2.
DELETE Statement 
7.
Frequently Asked Questions
7.1.
With which type of statement does the AND & OR clause work?
7.2.
Is it valid to use the AND & OR clauses in different combinations?
7.3.
What are some uses for SQL?
7.4.
What is an example of an RDBMS?
7.5.
What do clauses in a query mean?
8.
Conclusion
Last Updated: Mar 27, 2024
Medium

AND & OR Clause

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Extracting data using a simple query may not be easy when dealing with an extensive database. The solution to this problem is to find whether any SQL clauses exist that help gets data on the required conditions. One such clause that comes in handy in this kind of query is AND & OR clause.

AND & OR clause

So, let us learn how to use the AND clause & OR clauses from this article.

AND & OR clause

AND clause will help fetch data from the table where all the supplied conditions are true.

The OR clause will help get data from the table where any condition from the supplied conditions is true

AND clause & OR clause filter out the records with SELECT, INSERT, UPDATE, and DELETE statements. While using this clause in combining the conditions, it is essential to use parentheses so the database can figure out in which order conditions should be evaluated. 


Note: All the queries and questions in this blog apply to the following database Table called Table Employee.

 

Problem statement 1

Create the Employee’s table, which has all the employee data consisting of columns like EmployeeName, EmployeeAge, EmployeeSalary, Dept, and EmployeeID as the primary key (a set of values of the table that uniquely defines each record in this table). 


Query:

CREATE TABLE Employee (
  EmployeeID int, 
  EmployeeName varchar(255), 
  EmployeeAge int, 
  EmployeeSalary int, 
  Dept varchar(255)
); 


Output:

Result

 

Problem statement 2

Insert values into the table Employee


Query:

INSERT INTO Employee 
VALUES 
  ('01', 'Varun', '24', 20000, 'HR'), 
  ('02', 'Rahul', '23', 25000, 'IT'), 
  ('03', 'Sunny', '28', 30000, 'Marketing'), 
  ('04', 'Amit', '24', 25000, 'HR'), 
  ('05', 'Gaurav', '24', 40000, 'HR'), 
  ('06', 'Ravi', '30', 60000, 'Marketing'), 
  ('07', 'Aron', '28', 50000, 'IT'), 
  ('08', 'Sunny', '22', 10000, 'HR');


Output:

Result

 

Problem statement 3

View data in Employee table


Query:

SELECT 
  * 
FROM 
  Employee


Output:

Result

AND clause

The AND clause displays a record if all the conditions separated by AND are TRUE.

Truth Table

The below image describes the truth table for the AND clause:

AND Truth Table 

Syntax

SELECT ColumnName[s] , 
FROM TableName
WHERE condition1 AND condition2 AND condition3..n; 


Note: A comma will separate different column names.

 

Problem statement 4

Find all the employee names with salaries of more than 25000 and less than 60000 and print EmployeeSalary, EmployeeName.


SQL Query:

SELECT 
  EmployeeSalary, 
  EmployeeName 
FROM 
  Employee 
WHERE 
  EmployeeSalary > 25000 
  AND EmployeeSalary < 60000;


Output:

Result

 

Different operators can be used with AND, which also applies to the OR clause. 

operators

Let's make use of these operators to run the queries. 

 

Problem statement 5

Find all the employee names with ages not equal to 24 and less than equal to 30 and print EmployeeAge, EmployeeName.


SQL Query:

SELECT 
  EmployeeAge, 
  EmployeeName 
FROM 
  Employee 
WHERE 
  EmployeeAge != 24 
  AND EmployeeAge <= 30;


Output:

Result

OR clause

It allows many conditions to appear in a SQL query's WHERE clause.

If either of the requirements separated by OR is true, the OR clause outputs a record.

Truth Table

The below image describes the truth table for the OR clause.

OR Truth Table

Syntax

SELECT ColumnName[s] 
FROM TableName
WHERE condition1 OR condition2 OR condition3..n;


Note: A comma will separate different column names.

 

Problem statement 6

Find all the employee names belonging to either the IT or Marketing Department and print EmployeeName, EmployeeSalary, and Dept.


SQL Query:

SELECT 
  EmployeeName, 
  EmployeeSalary, 
  Dept 
FROM 
  Employee 
WHERE 
  Dept = 'Marketing' 
  OR Dept = 'IT';


Output:

Result


The following query will show how to use the different operators mentioned previously.

Problem statement 7

Find all the employee names whose age is odd or whose salary is less than equal to 30000 and print EmployeeName, EmployeeAge, and EmployeeSalary.


SQL Query:

SELECT 
  EmployeeName, 
  EmployeeAge, 
  EmployeeSalary 
FROM 
  Employee 
WHERE 
  EmployeeAge % 2 != 0 
  OR EmployeeSalary <= 30000;


Output:

Result

Combining AND & OR clause

AND and OR clauses can be combined to meet the query's requirements. 

Easy

Problem statement 8

Find all the employee names whose age is not equal to 24 and also need to find the salaries of employees over 50000 or less than 35000 and print EmployeeName, EmployeeAge, and EmployeeSalary.


SQL Query:

SELECT 
  EmployeeName, 
  EmployeeAge, 
  EmployeeSalary 
FROM 
  Employee 
WHERE 
  EmployeeAge != 24 
  AND (
    EmployeeSalary > 50000 
    OR EmployeeSalary <= 35000
  );


Output:

Result

Medium

Problem statement 9

Find all the employee names whose salary is less than 40000 or the employees whose age is less than 28 & greater than 23. We also need to print EmployeeName, EmployeeAge, and EmployeeSalary.

SQL Query:

SELECT 
  EmployeeName, 
  EmployeeAge, 
  EmployeeSalary 
FROM 
  Employee 
WHERE 
  EmployeeSalary < 40000 
  OR (
    EmployeeAge < 28 
    AND EmployeeAge > 23
  );


Output:

Result

Hard

Problem statement 10

Find all employees that are in HR dept but are not 24 years old and do not have salary greater than 20000


SQL Query:

SELECT 
  * 
FROM 
  Employee 
WHERE 
  (
    Dept = 'HR' 
    AND EmployeeAge != 24
  ) 
  OR (EmployeeSalary < 20000);


Output:

Result

Also see, Tcl Commands in SQL

Working of AND and OR clauses with different statements

The implementation of both clauses is the same for all statements like UPDATE, INSERT, and DELETE. 

Let's examine a few instances: 

UPDATE statement 

Problem statement 11

Update the employee salary to 75000 and change the employee's department to Hiring, whose age is equal to 23 and salary is less than 40000. 


SQL Query:

UPDATE 
  Employee 
SET 
  EmployeeSalary = 75000, 
  Dept = 'Hiring' 
WHERE 
  EmployeeAge = 23 
  AND EmployeeSalary < 40000;

 After the above query, no table will be printed.


Write a simple SQL query to print the entire table.

SELECT 
  * 
FROM 
  Employee


Result for the above query:

Result

DELETE Statement 

Problem statement 12

Delete employee’s data who are in the HR department or have salaries of more than 50000.


SQL Query:

DELETE FROM 
  Employee 
WHERE 
  Dept = 'HR' 
  OR EmployeeSalary > 50000;


Note: After the above query, no table will be printed. 

 

Write a simple SQL query to print the entire table.

SELECT 
  * 
FROM 
  Employee


Result for the above query:

Result

Frequently Asked Questions

With which type of statement does the AND & OR clause work?

A SELECT, INSERT, UPDATE, or DELETE query can test for many criteria by combining the AND and OR clauses.

Is it valid to use the AND & OR clauses in different combinations?

Using both clauses in one query is possible, but using parentheses is mandatory so the SQL can determine how to evaluate the conditions. 

What are some uses for SQL?

When speaking with a database, SQL is utilized. RDBMS use it as their standard language. For example, updating data in a database or retrieving data from a database are actions that are carried out using SQL statements.

What is an example of an RDBMS?

A relational database management system is referred to as an RDBMS. It's a system for managing information with a data model as its focal point. Here, all of the data is correctly recorded in tables. SQL Server, Oracle, MySQL, MariaDB, and SQLite are examples of RDBMS systems.

What do clauses in a query mean?

An SQL clause is a query component that enables you to filter or personalize how you want your data to be accessed.

Conclusion

This article discusses how to use AND & OR Clauses using various examples and write SQL queries. We also saw to use of both Clauses in combinations. Also, learn when to use these clauses based on queries asked. You can also refer to the below articles:


You may refer to our Guided Path on Code Studios for enhancing your skill set on DSACompetitive ProgrammingSystem Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninjas!

Live masterclass