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_CODE CUST_NAME CUST_STATE CUST_CATEGORY CUST_PHONE AGENT_CODE
C00001 Ravi Bharat Delhi Premium 9741321500 A009
C00002 Sunil Rai West Bengal Premium 9986741433 A005
C00005 Deepak Jadli Maharashtra Premium 9886381786 A002
C00013 Niki Mathur Delhi Gold 8842241971 A009
C00015 Faiyaz Ahmed West Bengal Basic 9083001143 A011
C00019 Arvin Kumar Tamil Nadu Basic 9535099644 A010
C00020 Rajesh Sinha Delhi Gold 9342777002 A004
C00021 Narayan Modala Tamil Nadu Premium 8108684494 A010
C00024 Sapna Dwivedi Maharashtra Basic 9688502998 A001
C00025 Yogesh Mistry Maharashtra Gold 9437727332 A001
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’.
C00001 Ravi Bharat Delhi Premium 9741321500 A009
C00002 Sunil Rai West Bengal Premium 9986741433 A005
C00005 Deepak Jadli Maharashtra Premium 9886381786 A002
C00013 Niki Mathur Delhi Gold 8842241971 A009
C00020 Rajesh Sinha Delhi Gold 9342777002 A004
C00021 Narayan Modala Tamil Nadu Premium 8108684494 A010
C00025 Yogesh Mistry Maharashtra Gold 9437727332 A001

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.
C00001 Ravi Bharat Delhi Premium 9741321500 A009
C00002 Sunil Rai West Bengal Premium 9986741433 A005
C00005 Deepak Jadli Maharashtra Premium 9886381786 A002
C00013 Niki Mathur Delhi Gold 8842241971 A009
C00015 Faiyaz Ahmed West Bengal Premium 9083001143 A011
C00019 Arvin Kumar Tamil Nadu Premium 9535099644 A010
C00020 Rajesh Sinha Delhi Gold 9342777002 A004
C00021 Narayan Modala Tamil Nadu Premium 8108684494 A010
C00024 Sapna Dwivedi Maharashtra Premium 9688502998 A001
C00025 Yogesh Mistry Maharashtra Premium 9437727332 A001

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;
C00001 Ravi Bharat Delhi Premium 9741321500 A009
C00005 Deepak Jadli Maharashtra Premium 9886381786 A002
C00013 Niki Mathur Delhi Gold 8842241971 A009
C00020 Rajesh Sinha Delhi Gold 9342777002 A004
C00024 Sapna Dwivedi Maharashtra Basic 9688502998 A001
C00025 Yogesh Mistry Maharashtra Gold 9437727332 A001

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_NUM POLICY_NUM POLICY_TERM CUST_CODE POLICY_STATUS POLICY_START
P200100 Life 15 years C00024 Active 5/15/2011
P200106 Pension 12 years C00020 Serviced 7/19/2000
P200111 Life 15 years C00013 Active 11/5/2009
P200120 Pension 15 years C00001 Active 8/16/2008
P200123 Pension 15 years C00002 Serviced 2/22/2000
P200124 Pension 15 years C00019 Serviced 3/21/2003
P200129 Life 35 years C00015 Active 1/11/2001
P200133 Life 27 years C00005 Active 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_CODE CUST_NAME CUST_PHONE POLICY_NUM
C00001 Ravi Bharat 9741321500 P200120
C00005 Deepak Jadli 9886381786 P200133
C00013 Niki Mathur 8842241971 P200111
C00024 Sapna Dwivedi 9688502998 P200100

Please get connected & share!

Advertisement