Mastering the OR Operator in SQL

Published : 07 July 2024
Updated : 07 July 2024

164 Views

OR operator is a fundamental logical operator used to combine multiple conditions in WHERE clause. This operator plays a crucial role in querying databases. You can use more than one OR operator in single SQL query based on your business requirement. This Operator provides flexibility by allowing developers to specify multiple criteria where at least one condition must evaluate to TRUE for the row to be included in the result set.

Syntax

SELECT column1, column2, ......
FROM table_name
WHERE condition1 OR condition2 OR ...........................;

OR operator is a fundamental logical operator. It returns TRUE if at least one condition is TRUE otherwise FALSE.
OR Operator is used to combine multiple conditions to perform operation on database table.
OR operator is used to specify multiple criteria to narrow down the result set.
OR Operator requires that at least one conditions separated by OR must evaluate to TRUE for the row to be included in the result set.
Use parentheses to group conditions for clarity and to control the order of operations .
OR operator evaluates conditions from left to right.
Try to avoid unnecessary OR operators to maintain query efficiency and readability.
OR operator uses indexes on columns used in WHERE clauses to optimize query performance, It is very helpful when dealing with large datasets.

Demo Database

we have 2 tables Users (Id, Name , Mobile ,Weight, DOB) and UserAddress (Id, UserId, AddressType, Address, Country, City and PostalCode)

CREATE TABLE Users (
    Id       INT           PRIMARY KEY,
    Name     VARCHAR(100)  NOT NULL,
    Mobile   VARCHAR(20),
    Weight   DECIMAL(9,2),
    DOB      DATE
);
CREATE TABLE UserAddress (
    Id         INT           PRIMARY KEY,
    UserId     INT           NOT NULL,
    AddressType VARCHAR(50)  NOT NULL,
    Address    VARCHAR(255)  NOT NULL,
    Country    VARCHAR(100)  NOT NULL,
    City       VARCHAR(100)  NOT NULL,
    PostalCode VARCHAR(20)   NOT NULL
);

AND vs OR

The OR operator displays a record if any of the conditions are TRUE.

The AND operator displays a record if all the conditions are TRUE.

OR with WHERE clause

OR is a fundamental logical operator which is mostly used with WHERE clause. It returns TRUE if at least one specified condition is TRUE otherwise FALSE.

SQL query to fetch users whose age is greater than 30 OR weight is less than 60.

SELECT Id, Name, Weight, DOB
FROM Users  
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE()) > 30 
      OR Weight < 60;

Multiple OR Operators

You can specify multiple OR operators in single SQL query to fetch relevant data based on your business requirement. if more than one OR conditions are present then row that satisfy at least one specified conditions will be displayed in the result set.

SQL Query to Retrieve Users with Permanent Addresses in India, Business Addresses in the USA or Temporary Addresses in Russia Whose Age is Greater than 30 and Weight is Less than 60

SELECT u.Id, u.Name, u.Weight, u.DOB, ua.AddressType, ua.Country
FROM Users u
JOIN UserAddress ua ON ua.UserId = u.Id
WHERE TIMESTAMPDIFF(YEAR, u.DOB, CURDATE()) > 30 
      AND u.Weight < 60 
      AND (
          (ua.AddressType = 'Permanent' AND ua.Country = 'India')
          OR (ua.AddressType = 'Business' AND ua.Country = 'USA')
          OR (ua.AddressType = 'Temporary' AND ua.Country = 'Russia')
      );

OR with Combining other Operators

OR operator is a fundamental logical operator. You can use OR with other operators. make sure to use parentheses to group conditions for clarity and to control the order of operation (OR operator evaluates conditions from left to right) when using OR with other operators it will be efficient and readable.

SQL query to retrieve users whose age is greater than 30 and has less than 60 weight OR a permanent Indian User OR Name start with `ra`.

SELECT u.Id, u.Name, u.Weight, u.DOB, ua.AddressType, ua.Country
FROM Users u
left JOIN UserAddress ua on ua.UserId = u.Id
WHERE TIMESTAMPDIFF(YEAR, u.DOB, CURDATE()) > 30 
      AND (u.Weight < 60 OR (ua.AddressType = "Permanent"
      AND ua.Country = "india") OR u.Name like "ra%");

Frequently Asked Questions (FAQs)

1. What does the OR operator do in SQL?

The OR operator allows you to combine two or more conditions in a WHERE (or HAVING) clause such that if any one of the conditions evaluates to TRUE, the row is included in the result set.

Example

SELECT * 
FROM Customers 
WHERE Country = 'Germany' OR Country = 'Spain';

2. How is OR different from AND?

With AND, all conditions must be TRUE for a row to appear.
With OR, at least one of the conditions must be TRUE

-- AND version
SELECT * FROM Students WHERE Age > 18 AND Grade = 'A';

-- OR version
SELECT * FROM Students WHERE Age > 18 OR Grade = 'A';

3. Can you mix OR and AND in the same query?

Yes , you can mix them, but you must use parentheses to ensure the correct logic is applied, because AND takes precedence over OR in SQL evaluation unless overridden by parentheses.

SELECT * 
FROM Employees 
WHERE (Department = 'IT' AND City = 'New York') 
   OR Salary > 70000;

4. Are there performance concerns with using OR?

Yes , when you use OR across many conditions, especially on different columns, it can prevent index usage or cause full table scans, which may degrade performance. If performance becomes a problem, consider rewriting using UNION or ensuring each OR-condition uses indexed columns.

5. Is there a limit to how many OR conditions you can use?

Technically, most SQL engines don’t impose a strict small limit on number of OR conditions, you can use many. But from a practical point of view, having too many OR conditions makes your query complex, harder to maintain and may degrade performance.

Tip : If you find yourself with dozens of OR-conditions, perhaps use a lookup table + join or switch to IN (…) when applicable.

6. Can OR be used in other statements besides SELECT?

Yes , you can use OR in UPDATE, DELETE or any statement that supports WHERE (and in some dialects HAVING). For example:

UPDATE Users 
SET Status = 'Archived' 
WHERE LastLoginDate < '2024-01-01' OR IsInactive = 1;

7. What about NULL and OR? Does OR handle NULLs specially?

Yes , because SQL uses three-valued logic (TRUE/FALSE/UNKNOWN), if one of the conditions is NULL, it may evaluate to UNKNOWN, which is treated as not TRUE, so the row may be excluded.

Tip : If a column might be NULL, explicitly include … OR Column IS NULL if you intend to include those rows or use COALESCE if suitable.