SQL OR Operator

An infographic journey through the logical power of the OR operator. Explore inclusive data retrieval, dynamic filtering, and flexible query conditions.

What is SQL OR operator?

The “OR” is a logical operator in SQL that is used to combine two or more conditions in the WHERE clause.

The WHERE clause with the OR operator can be used with SELECT, UPDATE, INSERT, and DELETE statements. For example:

WHERE ID = 1 OR ID = 15 OR ID = 20;
  • As using multiple conditions with the OR operator, it returns TRUE if any of the conditions is TRUE.
  • So, if the OR operator is used with the SELECT statement, it returns records if any of the conditions are TRUE.
  • In the case of the AND logical operator, all the conditions have to be evaluated as TRUE, otherwise, it returns FALSE.
  • If you are using the ‘OR’ and ‘AND’ operators in a single query then OR is evaluated after the AND operator unless parenthesis is used.
  • I will show you this and other examples of OR operator in the next section.

A basic example of using OR operator

In the first example of the OR operator, I am using the sto_employees table.

In the SELECT statement with WHERE clause, I used the ID column with the OR operator for multiple conditions as follows:

Query:

SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE id=2 OR id=5 OR id=8 OR id=20;

SQL IN operator

From the recordset, this is evident that although the id=20 is not found. The query returned records for the IDs that are found in the table.

If we used the AND operator in above query; none of the records should have returned. That is:

SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE id=2 AND id=5 AND id=8 AND id=20;

The result:

SQL IN AND

The result is an empty recordset.

Using different columns example

In this query, I used different columns for defining the condition in the WHERE clause and combined those with the OR operator.

The emp_name is, obviously, a text field while the emp_salary is a numeric field:

SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE emp_name = 'Mike' OR emp_salary >=5000;

The resultset:

SQL IN text

The example of using AND / OR together

The following query shows using the AND & OR operators together.

To see the difference, I am using almost the same queries except for the order of columns in the WHERE clause is changed.

See the queries and resultsets:

SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE emp_salary >=5000 OR emp_name = 'Mike' AND id=20;



SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE emp_name = 'Mike' OR emp_salary >=5000 AND id=20;

SQL IN and AND

Using parenthesis for changing the order of evaluation

For changing the order of evaluation of OR & “AND” operators, you may enclose the OR in parentheses.

The following query explains how it works.

The query is to fetch the records of employees whose salary must be greater than 5500 and employee name must be Mike or Johny. See how it worked:

SELECT id, emp_Name, emp_age, emp_salary

FROM sto_employees

WHERE emp_salary >=5500 AND (emp_name = 'Mike' OR emp_name = 'Johny');

SQL IN and order

The example of using the OR operator with the DELETE command

The following query shows using the SQL OR operator with the DELETE command.

As mentioned earlier, you may use the OR operator with SELECT, UPDATE, INSERT, and DELETE commands for retrieving or affecting only specific rows with multiple conditions.

This query removes records of employees whose id is equal to 11 or 9:

DELETE FROM sto_employees

WHERE id = 9 OR id = 11;

SQL IN DELETE

The second table graphic shows the table with removed rows.

Using OR operator with a HAVING clause

The HAVING clause acts like the WHERE clause, however, this is used for filtering the aggregated results in the SELECT statement.

For combining multiple conditions with the HAVING clause, you may also use the IN operator just like the WHERE clause.

The following query shows how; by enquiring the sto_emp_salary_paid table that keeps the records of salary paid to employees every month.

We get the total amount paid by using the GROUP BY clause and apply a filter by HAVING clause with the OR operator:

SELECT emp_name As "Employee Name", SUM(emp_sal_paid) As "Paid Salary"

FROM sto_emp_salary_paid

GROUP BY emp_name

HAVING SUM(emp_sal_paid) = 4500.50 OR SUM(emp_sal_paid) = 9001.33;

You may learn more about the SQL HAVING clause in its tutorial.

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!