AND & OR Clause

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:

 

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:

 

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

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:

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.

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:

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:

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:

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:

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:

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:

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:

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:

• Mysql-introduction
• Mysql-features
• Mysql-installation
• Mysql-connection
• SQL Practice Problems
• Sql functions

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System 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!