Thursday, October 5, 2017

TSQL - Logical OR and AND Operations in SQL Server

In SQL Server, logical operators are used to test for the truth of some condition. Based on the expressional conditions, they return a true or false value to combine one or more true or false values.

Logical Operator OR - If you are using logical operator OR that means any one or both conditions will be TRUE. In this case, if first condition is TRUE then second condition will also checked it which can be either TRUE or FALSE. 

Logical Operator AND - If you are using AND logical operator then both condition should be true for the results. We can say that if first condition is false then second condition will be checked also. 

When you are working some data queries then there are few chances to miss out simple logic order. If you are not writing your queries carefully then AND and OR statements can greatly change a data set. We can avoid common SQL bugs by either using parenthesis or organising our statements to represent the logic that should be applied. 
To understand these operators, we can take an example of customer data as given below-
CustId
CustName
CustEmail
CustAddress
CustMobile
101
Ryan Arjun
ryan.arjun@gmail.com
New Delhi, India
2541563214
102
Kimmy Wang
kwang@htc.com
New York, USA
4156321425
103
Bill Smith
bill.smith@google.com
Sydney, Australia
1562543214
104
Rose Gray
roseg@dell.com
British Columbia, Canada
5632125414
105
John Day
dayh@google.com
London, UK
4569870671
If we want to pull the data for customer where customer name like ‘Ryan%’ and Address like ‘%USA%’ or email like ‘%google%’ then how can we use these both logical operators (AND or OR) as given below-
----- Select statements
SELECT [CustId]
      ,[CustName]
      ,[CustEmail]
      ,[CustAddress]
      ,[CustMobile]
  FROM [dbo].[Tbl_Customer_Master]
  ----- customer name  start with Ryan
  where ([CustName] like 'Ryan%'
----- customer Address  like USA
  AND [CustAddress] like '%USA%')
----- customer email with google
  OR [CustEmail] like '%google%'

----- Output Result
CustId
CustName
CustEmail
CustAddress
CustMobile
103
Bill Smith
bill.smith@google.com
Sydney, Australia
1562543214
105
John Day
dayh@google.com
London, UK
4569870671
Conclusion
Logical operators compare two conditions at a time to determine whether a row can be selected for the output. They can change the result output if you are not using them carefully.

Popular Posts