Colleague says to never use an OR statement in SQL, is this true?

Question

My colleague now responsible for SQL development says that I should never use an OR statement because it will mess up the query optimizer and ignore table indexes producing slow queries. I couldn't find any example of this while googling. The alternative to the following query becomes really ugly with a dozen or so blocks of code which look almost identical(to the example) using if else statements for each variable state. Note the variables that are checked to short-circuit and return all results if the value is 2 or else filter by the field.

I asked for some resources containing these claims about why not to use an OR statement and received the following links (we are using MS SQL Server).

• https://stackoverflow.com/questions/5639710/union-all-vs-or-condition-in-sql-server-query
• https://bertwagner.com/2018/02/20/or-vs-union-all-is-one-better-for-performance/
• http://sqlserverplanet.com/optimization/using-union-instead-of-or

None of these examples seem to resemble the current implementation as below. I find it hard to believe that this code is problematic but please let me know if it is. I would also like some more information where the comment made about not using OR might actually hold true and why so, as to better understand the issue.

SELECT
    e.EmployeeName,
    e.DepartmentName,
    crs.Title,
    c.Name as CompanyName
FROM Employee E
Left Outer Join Company c ON c.Id = @companyId
    INNER JOIN Department d on e.DepartmentId = d.Id 
WHERE   
    c.Id = @companyId           
    AND (@Active = 2 OR  crs.IsActive = @Active)
    AND (@Dot = 2 OR IsDot = @Dot)
    AND crs.CompanyId = @companyId
    AND d.CompanyId = @companyId
ORDER BY EmployeeName, Title, PassedDate

I am a believer that duplicating code is always bad unless there is a really good reason. After testing the query I confirmed that the proper indexes were being used. After mentioning this, I was told that he would use best practices. I haven't seen any best practices telling me not to use OR. Can anyone lead me to these?

---

Here is the abomination before I updated it a long time ago. If you caught the @Department and are wondering about it, it is not an error. A Telerik reporting component is doing something to this code and expanding an array in the background before it gets to the server.

IF @Active = 2
BEGIN
--ACTIVE AND INACTIVE
    IF 0 IN (@Department)
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1 

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,           
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
            BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,           
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D       ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
    ELSE
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1
                    AND A.DepartmentId IN (@Department)

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE   A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
END
ELSE
BEGIN
--ACTIVE OR INACTIVE
    IF 0 IN (@Department)
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1 

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
            BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D       ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
    ELSE
    BEGIN
        IF @DOT = 1 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 1
                    AND A.DepartmentId IN (@Department)

            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 0 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END

        IF @DOT = 2 
        BEGIN
            Select 
            A.LastName + ', ' + A.FirstName as EmployeeName,
            A.DepartmentName,
            C.Title,
            ISNULL(B.Comments, ' ') as Remarks,
            CONVERT(varchar, B.PassedDate, 101) as DateOut,
            D.Name as CompanyName

            FROM Employee A
            Left Outer Join EmployeeCourse B    ON A.Id = B.EmployeeId
            Left Outer Join CompanyCourse C     ON B.CompanyCourseId = C.Id
            Left Outer Join Company D           ON @companyId = D.Id

            WHERE  A.IsActive = @Active 
                    AND A.CompanyId = @companyId 
                    AND B.PassedDate IS Not NULL 
                    AND C.DotCourse = 0 
                    AND A.DepartmentId IN (@Department)
                    
            ORDER BY EmployeeName, Title, PassedDate
        END 
    END
END

NOTE: I removed some in the initial code example to simplify.

According to his explanation, links, and my searches I find it hard to believe that my current solution is not the best for performance and readability. I accept that there may be some cases out there that risk performance degradation but I highly doubt that this is the case with my implementation. I don't like the idea that OR should never be used when I haven't seen any documentation that says this.

When I showed the query I was told as a generalization to never use the OR. I am now doing my homework to see if this is valid information. I would really hate to use the previous code as it is wrong is so many ways.

Answer 1

Never listen to anyone saying that you should never do X.

Generally, you should not try to outsmart the query optimizer without some really good reasons.

It is true that in certain scenarios too many ORs can lead to a suboptimal¹ plan, but you should consider every such scenario individually and only look for workarounds if the original query performance is unacceptable.

If you do need to address performance of the query you posted, please consider asking a different question.

---

1 - From the human point of view. The plan in fact will be optimal for that particular query variant (to the extent of the optimizer capabilities); what I mean is that rewriting the query might produce a different plan that executes faster or consumes fewer resources.

Answer 2

It's not that you should never use OR in a where clause, it's that the particular pattern you're following in this case is a very bad one. I've written and recorded on the subject:

• Optional Parameters and Missing Index Requests
• The SQL Server Performance Tasting Menu: Optional Parameters

Additionally, the pattern you chose to replace it with will not work out the way you think. See here:

• SQL Server--If logic in stored procedure and the plan cache
• The Not Very Mighty IF Branch

Joins with or clauses can also be difficult:

• Joins With OR Clauses = 💔

Under different circumstances, e.g. not using optional parameters, OR can be used to little detriment, as long as you have indexing to support the predicates. I'm not saying it's always best, but it's workable.

There are many times you'll find yourself in a better position by using UNION ALL to replace OR, but in the specific case you have, you're better off using dynamic SQL to build the appropriate query and execute it. That technique is covered in the video I linked to.

Answer 3

Picking up from comments on Erik's answer:

You basically have the ol' optional parameters case. It seems you expect us to tell you which option is best. We can't do that since we aren't in your shoes. What we can do is to outline a few alternatives for you to investigate and then determine which is better your your particular situation.

Branching with IF and hand-crafting each query shape. As long as you are aware that parameters passed to a proc will be used when the proc-plan is generated for all queries, regardless of your branching code. I.e., you end up with for instance the second query at run time when plan is generated, but plans are generated for all queries. Chances are that the other plans now will have out-of-whack selectivity estimates. Just imagine the proc without all the branching logic. This is what the optimizer will see. You might end up with the "sometimes it is fast, sometimes it is slow" situation. Options here for you are OPTIMIZE FOR to get "plan stability" and OPTION(RECOMPILE), although the last one might not be interesting if you keep reading.

Using your query shape with OR and throwing OPTION(RECOMPILE) to allow for index usage. But are you prepared to pay for plan generation on each execution? That is for you to answer.

Using dynamic SQL with sp_executesql so you end up with generated SQL matching your various query shapes. You now have possibility of index usage and parameter sniffing the "real" values. But dynamic SQL has its drawback. To be licensed to use dynamic SQL, one should have read Erland's article on the topic first. :-)

So, as it often is, there are upsides and downsides with all alternatives. Erland's article has a section on optional search arguments. Reading that article is a good start, hopefully allowing you to decide which alternative(s) is best for your particular situation.