SQL AND & OR Operators

SQL AND and OR logical operators can be used to combine multiple conditions on a WHERE clause, to filter records on a database table.

  • These are generally used along with the WHERE and HAVING clauses that define conditions to filter records.

In the previous chapters, we have seen how to filter records based on a single condition using the WHERE clause.

Now, let's see how to filter records based on multiple conditions using AND and OR logical operators along with the WHERE clause.

Combine Conditions

These are logical operators, which can be used to combine two conditions on a WHERE clause to filter records on a table.

  • The AND operator returns TRUE only if both the conditions are TRUE.
  • The OR operator returns TRUE if at least one of the conditions is TRUE.
  • Commonly used on queries like SELECT, UPDATE, DELETE, etc.,

Syntax

Here is the basic syntax for the AND operator on queries SELECT, UPDATE, and DELETE.

SELECT * FROM table_name 
WHERE condition1 AND condition2;
UPDATE table_name 
SET column = value 
WHERE condition1 AND condition2;
DELETE FROM table_name 
WHERE condition1 AND condition2;

Here is the basic syntax for the OR operator on queries SELECT, UPDATE, and DELETE.

SELECT * FROM table_name 
WHERE condition1 OR condition2;
UPDATE table_name 
SET column = value 
WHERE condition1 OR condition2;
DELETE FROM table_name 
WHERE condition1 OR condition2;

Table Data

Let's assume the below data on the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

WHERE Clause with AND Operator

Use the below example to retrieve employees that satisfy both the below conditions, connected using the AND operator.

  • Employee ID values among (1, 3, 5).
  • And the salary of less than 30000.

Run this on IDE

SELECT * FROM employees 
WHERE employee_id IN (1,3,5) AND salary < 30000;

After successful execution, the output looks like this.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000

WHERE Clause with OR Operator

Use the below example to retrieve employees that satisfy either of the below conditions, connected using the OR operator.

  • Employee ID values among (1, 3, 5).
  • Or the salary of less than 30000.

Run this on IDE

SELECT * FROM employees 
WHERE employee_id IN (1,3,5) OR salary < 30000;

After successful execution, the output looks like this.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

WHERE Clause with both AND & OR Operators

Use the below example to retrieve employees that satisfy the below conditions, connected using the AND and OR operators.

  • Employees with ID values among (1, 3, 5) and salary less than 30000.
  • Or employee's name starts with the word 'James'.

Run this on IDE

SELECT * FROM employees 
WHERE (employee_id IN (1,3,5) AND salary < 30000) 
OR employee_name LIKE 'James%';

After successful execution, the output looks like this.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-06-21 45000

Other Statements

In the above examples, we have used the AND and OR operators on SELECT statements.

Similarly, these operators can be used on UPDATE and DELETE statements.

Overall

We now know how to combine conditions using AND and OR operators on the WHERE clause.

Related Links