How to use AND/OR effectively in SQL Server

Question

I am new to programming and this is my first question, so I apologise if I make a mistake.

I have written this query:

   SELECT U.Id, UWH.Role, USI.Title, CAST(USI.StartDate AS DATE)
   FROM UserWorkHistory UWH
   JOIN Users U ON UWH.UserId=U.Id
   JOIN UserStoryItems USI ON U.Id=USI.UserId
   JOIN UserWorkHistoryTypes UWHT ON UWH.UserWorkHistoryTypeId=UWHT.Id
   WHERE U.Location LIKE '%Great Britain%'
         OR U.Location LIKE '%United Kingdom%'
         OR U.Location LIKE '%England%'
         OR U.Location LIKE '%UK%'
         OR U.Location LIKE '%U.K.%'
         AND UWHT.Id = 1
         AND USI.Id = 1
         AND CAST(USI.StartDate AS DATE) > DATEADD(YEAR,-5,GETDATE())
         AND UWH.Role LIKE '%Contract%'
         OR UWH.Role LIKE '%Contractor%'
         OR UWH.Role LIKE '%Freelance%'
         OR UWH.Role LIKE '%Non-perm%'
         OR UWH.Role LIKE '%non-permanent%'
         OR USI.Title LIKE '%Contractor%'
         OR USI.Title LIKE '%Contractor%'
         OR USI.Title LIKE '%Freelance%'
         OR USI.Title LIKE '%Non-perm%'
         OR USI.Title LIKE '%non-permanent%'
         OR USI.Title LIKE '%self-made%'

I am trying to specify four things:

1) That the results I get back are from the UK

2) That the results I get back contain any of the words I have specified in the 'LIKE' arguments.

3) That the results adhere to the rules (UWHT.Id = 1, USI.Id = 1).

4) That only the results with a StartDate from the last 5 years are returned to me.

Apart from the locations, nothing else is returning as I would like. I would imagine that it's because of an incorrect AND/OR syntax, but can't find a previous SO question that explains how to do this (if there is one and I've missed it, I apologise).

Possible duplicate of [SQL Logic Operator Precedence: And and Or](http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — default locale, Feb 02 '17 at 13:36
Also consider putting the location strings, roles and titles in their own tables an JOIN with LIKE, that way the query is simpler and its easy to add/remove things without needing to change the query itself. — Alex K., Feb 02 '17 at 13:37
Best hope you don't have any data for anyone in the `UKraine`. — Damien_The_Unbeliever, Feb 02 '17 at 13:41
Yes, that makes a lot of sense! Thank you so much everyone. I will try once with lots of parenthesis and once with separate tables so I can get better at both ways. — BadAtCoding, Feb 02 '17 at 13:41

rory.ap · Answer 1 · 2017-02-02T13:47:01.953

The first thing I see wrong is that you aren't using parens anywhere in your where clause. You want to group the ORs with their associated ANDs:

WHERE 
    (U.Location LIKE '%Great Britain%'
    OR U.Location LIKE '%United Kingdom%'
    OR U.Location LIKE '%England%'
    OR U.Location LIKE '%UK%'
    OR U.Location LIKE '%U.K.%')
AND UWHT.Id = 1
AND USI.Id = 1
AND CAST(USI.StartDate AS DATE) > DATEADD(YEAR,-5,GETDATE())
AND 
    (UWH.Role LIKE '%Contract%'
    OR UWH.Role LIKE '%Contractor%'
    OR UWH.Role LIKE '%Freelance%'
    OR UWH.Role LIKE '%Non-perm%'
    OR UWH.Role LIKE '%non-permanent%'
    OR USI.Title LIKE '%Contractor%'
    OR USI.Title LIKE '%Contractor%'
    OR USI.Title LIKE '%Freelance%'
    OR USI.Title LIKE '%Non-perm%'
    OR USI.Title LIKE '%non-permanent%'
    OR USI.Title LIKE '%self-made%')

The reason is that there is an order of operations. Let's take a simpler example, in English: What if I wanted to ask someone, "Can you give me a list of things you need at the store which are in the dairy isle or are in the frozen foods isle and cost below $3.00 or cost above $7.00?"

The meaning of that is ambiguous. For instance, you could interpret that as, "Can you give me a list of things...which are in the dairy isle (price doesn't matter) OR which are in the frozen foods isle as long as they (the frozen food items) cost below $3.00 or above $7.00?" You could also interpret it as, "Can you give me a list of things...which are in the dairy isle or the frozen foods isle and cost below $3.00 OR are in ANY isle and cost above $7.00?"

There are a handful of interpretations besides the one I just illustrated. SQL Server has rules, so it always interprets based on a particular order of operations, which may not necessarily be clear and thus leads to bugs. To overcome this, you use parens to force it to be interpreted in a certain way:

"Can you give me a list of things which 1.) are in the dairy isle or the frozen food isle, and 2.) cost below $3.00 or above $7.00?"

In the question, the 2nd point says ( That the results I get back do not contain any of the words I have specified in the 'LIKE' arguments.) so I think we have to change LIKE to NOT LIKE, am i right? — PowerStar, Feb 02 '17 at 13:43
@balaji You're right - I actually have two queries that are identical save for one is LIKE and one is NOT LIKE, to try and get two different results from the same dataset. I then copied and pasted the wrong one into the question. I will edit the original question to change this. Good spot! — BadAtCoding, Feb 02 '17 at 14:03

score 1 · Answer 2 · answered Feb 02 '17 at 13:37

AND takes precedence over OR. You should group your AND and OR statements:

Select  U.Id,
        UWH.Role,
        USI.Title,
        Cast(USI.StartDate As Date)
From    UserWorkHistory      UWH
Join    Users                U    On UWH.UserId = U.Id
Join    UserStoryItems       USI  On U.Id = USI.UserId
Join    UserWorkHistoryTypes UWHT On UWH.UserWorkHistoryTypeId = UWHT.Id
Where   
(
    U.Location Like '%Great Britain%'
 Or U.Location Like '%United Kingdom%'
 Or U.Location Like '%England%'
 Or U.Location Like '%UK%'
 Or U.Location Like '%U.K.%'
)
And    UWHT.Id = 1
And    USI.Id = 1
And    Cast(USI.StartDate As Date) > DateAdd(Year, -5, GetDate())
And 
(
    (
        UWH.Role Like '%Contract%'
     Or UWH.Role Like '%Contractor%'
     Or UWH.Role Like '%Freelance%'
     Or UWH.Role Like '%Non-perm%'
     Or UWH.Role Like '%non-permanent%'
    )
    Or 
    (
        USI.Title Like '%Contractor%'
     Or USI.Title Like '%Contractor%'
     Or USI.Title Like '%Freelance%'
     Or USI.Title Like '%Non-perm%'
     Or USI.Title Like '%non-permanent%'
     Or USI.Title Like '%self-made%'
    )
);

Like @Siyual indicated, you need to understand the operator precedence in Transact-SQL. See https://msdn.microsoft.com/en-us/library/ms189773.aspx and https://msdn.microsoft.com/en-us/library/ms190276.aspx Use parentheses for finer control. Best advice, code the where clause bit-by-bit, you'll quickly learn how the AND and OR operators work. — Steve, Feb 02 '17 at 14:10

How to use AND/OR effectively in SQL Server

2 Answers2