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

Answer 1

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

Answer 2

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?"