SQL Server OR

The objective of this tutorial is to teach you how to use the OR operator to evaluate multiple conditions in a query.

What is the OR operator in SQL Server?

The OR operator is used when you have to evaluate multiple conditions but all the conditions need not evaluate to TRUE for the consequent action. Any one condition evaluating to TRUE suffices.

Operation

The SQL Server OR operator is used with and follows the WHERE keyword in a SQL query. A SQL statement can contain one or more OR operators to string together multiple conditions for evaluation and the condition check can be performed on one or more tables.

It can be used with SELECT, UPDATE and DELETE queries. When used with SELECT it returns all those records where at least one condition is satisfied. When used with UPDATE it updates the values for the specified columns if at least one specified condition is satisfied. When used in DELETE query it deletes records from the table if at least one specified condition is satisfied.

SQL Server OR syntax

The basic syntax of SQL Server OR operator is as follows.

SELECT expressions
FROM tables
WHERE [condition OR condition X];

In this syntax,

  • expressions – expressions defined here are the column(s) or calculations you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
  • tables – one or more than one table from where you want to retrieve data.
  • WHERE conditions – Mandatory with OR. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
  • OR –This is used to specify one or more conditions with the where clause and at least one of them must evaluate to TRUE.

SQL Server OR operator Examples

Let us see how it works in the different scenarios.

NOTE: Columns are also referred to as fields or attributes and the terms are used interchangeably.

Suppose we have a CUSTOMERS table containing the data of customers who have purchased insurance from an insurance company. We will be querying the same in the examples that follow using the OR operator to understand its usage.

CUST_CODECUST_NAMECUST_STATECUST_CATEGORYCUST_PHONEAGENT_CODE
C00001Ravi Bharat                  Delhi              Premium                 9741321500A009 
C00002Sunil Rai                    West Bengal        Premium                 9986741433A005 
C00005Deepak Jadli                 Maharashtra        Premium                 9886381786A002 
C00013Niki Mathur                  Delhi              Gold                    8842241971A009 
C00015Faiyaz Ahmed                 West Bengal        Basic                   9083001143A011 
C00019Arvin Kumar                  Tamil Nadu         Basic                   9535099644A010 
C00020Rajesh Sinha                 Delhi              Gold                    9342777002A004 
C00021Narayan Modala               Tamil Nadu         Premium                 8108684494A010 
C00024Sapna Dwivedi                Maharashtra        Basic                   9688502998A001 
C00025Yogesh Mistry                Maharashtra        Gold                    9437727332A001 
Customers Table (Containing data of customers)

1) SQL Server OR example – Select Query

The following SELECT statement evaluates 2 conditions and returns those rows in the resultset where both or one of the conditions is met.

SELECT * FROM customers
WHERE cust_category=’Gold’
OR cust_category=’Premium’;

The output generated by the query is below and includes all records excluding those rows where the customer category is ‘Basic’.

C00001Ravi Bharat                  Delhi              Premium                 9741321500A009 
C00002Sunil Rai                    West Bengal        Premium                 9986741433A005 
C00005Deepak Jadli                 Maharashtra        Premium                 9886381786A002 
C00013Niki Mathur                  Delhi              Gold                    8842241971A009 
C00020Rajesh Sinha                 Delhi              Gold                    9342777002A004 
C00021Narayan Modala               Tamil Nadu         Premium                 8108684494A010 
C00025Yogesh Mistry                Maharashtra        Gold                    9437727332A001 

2) SQL Server OR example – Update Query

The following UPDATE query replaces the customer category to Premium for records where the customer is either from the state of Maharashtra or is a Basic category customer.

UPDATE customers
SET cust_category='Premium'
WHERE cust_state='Maharashtra'
OR cust_category='Basic';

Output

If we run a select query after the update, we will see there are no customer records where the customer category is Basic including the one the Maharashtra Gold customer. All have been upgraded to Premium.

SELECT * FROM customers;

The output is given below.

C00001Ravi Bharat                  Delhi              Premium                 9741321500A009 
C00002Sunil Rai                    West Bengal        Premium                 9986741433A005 
C00005Deepak Jadli                 Maharashtra        Premium                 9886381786A002 
C00013Niki Mathur                  Delhi              Gold                    8842241971A009 
C00015Faiyaz Ahmed                 West Bengal        Premium                                    9083001143A011 
C00019Arvin Kumar                  Tamil Nadu         Premium                                    9535099644A010 
C00020Rajesh Sinha                 Delhi              Gold                    9342777002A004 
C00021Narayan Modala               Tamil Nadu         Premium                 8108684494A010 
C00024Sapna Dwivedi                Maharashtra        Premium                 9688502998A001 
C00025Yogesh Mistry                Maharashtra        Premium                 9437727332A001 

3) SQL Server OR example – DELETE Query

The following DELETE query deletes rows from the table where the customer is from the state of West Bengal or where the agent associated with the customer has agent code A010.

DELETE FROM customers
WHERE cust_state='West Bengal'
OR agent_code='A010';

Output

If we run a select query after the delete operation, we will see that there are no records for the above-mentioned state and agent.

SELECT * FROM customers;
C00001Ravi Bharat                  Delhi              Premium                 9741321500A009 
C00005Deepak Jadli                 Maharashtra        Premium                 9886381786A002 
C00013Niki Mathur                  Delhi              Gold                    8842241971A009 
C00020Rajesh Sinha                 Delhi              Gold                    9342777002A004 
C00024Sapna Dwivedi                Maharashtra        Basic                   9688502998A001 
C00025Yogesh Mistry                Maharashtra        Gold                    9437727332A001 

3) SQL Server OR example – multiple tables querying with JOIN

Let us consider the above-mentioned customers table and a below table called policies which contains the policy information of the policies purchased by the customers. We will be querying the tables after joining them using the OR operator.

POLICY_NUMPOLICY_NUMPOLICY_TERMCUST_CODEPOLICY_STATUSPOLICY_START
P200100  Life                    15 years      C00024Active        5/15/2011
P200106  Pension                 12 years      C00020Serviced      7/19/2000
P200111  Life                    15 years      C00013Active        11/5/2009
P200120  Pension                 15 years      C00001Active        8/16/2008
P200123  Pension                 15 years      C00002Serviced      2/22/2000
P200124  Pension                 15 years      C00019Serviced      3/21/2003
P200129  Life                    35 years      C00015Active        1/11/2001
P200133  Life                    27 years      C00005Active        9/23/1992
Policies Table (Containing policy data of customers)

The following SELECT query selects specified columns from both tables joined by an inner join where either the policy type or policy term is as specified.

SELECT
customers.cust_code
,customers.cust_name
,policies.policy_num
FROM customers
INNER JOIN policies
ON customers.cust_code=policies.cust_code
WHERE policies.policy_type='Life'
OR policies.policy_term='15 years';

Output

The output is given below and includes 4 records of customers having a life insurance policy of 15 years term.

CUST_CODECUST_NAMECUST_PHONEPOLICY_NUM
C00001Ravi Bharat                  9741321500P200120  
C00005Deepak Jadli                 9886381786P200133  
C00013Niki Mathur                  8842241971P200111  
C00024Sapna Dwivedi                9688502998P200100  

Advertisement