OR Operator in SQL

DevSniper
Published : 07 July 2024

160 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)

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%");