Can I use an IF statement in the WHERE clause to determine what ANDs will be part of the query?

Question

I have an SQL statement that has several ANDs in the WHERE cluse and I want to know if I can have an IF THEN ELSE statement under the WHERE clause to determine what AND will execute.

Three parameters will be passed to this query

WHERE  tlv.active = 1
AND tli.active = 1
AND t.endWorkShiftId IS NOT NULL
AND t.voidTypeId IS NULL
AND tlv.customerId = 1
AND tlv.locationId = 1
AND tlv.dockId = 3

IF ( :referenceId IS NULL ) {
    AND tlv.vendorCldcId = :vendCldId
    AND tlv.vendorId = :vendId
}
ELSE {
    AND vru.refId = :referenceId
}

My bad, this is MySQL and I use HeidiSQL – Eduardo Serrano Feb 08 '18 at 21:04 — Eduardo Serrano, Feb 08 '18 at 21:04

Codo · Answer 1 · 2018-02-08T21:28:21.420

No, there is no IF. And CASE isn't applicable either. But you can use a more complex boolean expression:

WHERE  tlv.active = 1
AND tli.active = 1
AND t.endWorkShiftId IS NOT NULL
AND t.voidTypeId IS NULL
AND tlv.customerId = 1
AND tlv.locationId = 1
AND tlv.dockId = 3
AND (
    (
        :referenceId IS NULL
        AND tlv.vendorCldcId = :vendCldId
        AND tlv.vendorId = :vendId
    ) OR (
        :referenceId IS NOT NULL
        AND vru.refId = :referenceId
    )
)

Update

By the way: This kind of query is a performance anti-pattern. The complex expression prevents efficient table joins in most database systems.

Instead of this, you should create two separate, much simpler queries: one for referenceId IS NULL and one for referenceId IS NOT NULL and call either one or the other.

Dai · Answer 2 · 2018-02-08T20:48:00.557

No, because IF is an imperative language construct, whereas a SQL query is defined in terms of logical-predicates (which is what enables the automatic optimization of query execution plans, for example). So you can achieve the same effect, you just need to "think functionally" (as in Functional Languages, like Haskell) - with the added difficulty of ensuring your resultant query is still Sargable.

In your case, you probably want this:

...
AND
tlv.dockId = 3
AND
(
    (
        :referenceId IS NULL
        AND
        tlv.vendorCldcId = :vendCldId
        AND
        tlv.vendorId = :vendId
    )
    OR
    (
        :referenceId IS NOT NULL
        AND
        vru.refId = :referenceId
    )
)

Note that depending on the DBMS this may have a suboptimal execution plan because the query engine might not optimize-away the alternative OR condition when depending on :referenceId, especially in the case of MSSQL Server:

...the general recommendation seems to be to write individual queries for each possible set of parameters.

Can I use an IF statement in the WHERE clause to determine what ANDs will be part of the query?

2 Answers2