What is SQL OR operator?
The WHERE clause with the OR operator can be used with SELECT, UPDATE, INSERT, and DELETE statements. For example:
- 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;
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:
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:
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;
Using parenthesis for changing the order of evaluation
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');
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;
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.
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.