There are various approaches one could take to conditional conditions of the form if-then-else, each of which differs in readability and performance. All approaches can also handle conditions of the form if-then by using a true value (1) for the else branch (as if p then q is logically equivalent to if p then q else true).
(Note: the condition shown in the question's code sample asserts that closedDate should be NULL when @Closed is true; this appears to be a logic mistake, as @Closed should be true to get closed orders, in which case closedDate shouldn't be null, as the question text rightly states. In this answer, the NULL tests are swapped to correct this error.)
IF Function
Some RDBMSs have an IF function, which is closest to the question sample and conceptually simplest.
IF(<test>, <then condition>, <else condition>)
SQL Server provides IIF, which is equivalent to a CASE expression (see below). Since @Close basically contains a boolean value, it can be tested directly, rather than comparing to 1. In the more general case, a comparison expression would be used.
IF(@Closed, o.ClosedDate IS NOT NULL, o.ClosedDate IS NULL)
Note that since this uses column values within a function, queries using IF generally aren't sargable.
CASE Operator
An IF function is fairly simple: a test picking among two alternatives. CASE expressions are a more general, allowing for an arbitrary number of alternatives. Consequently, you have a choice of what values to handle as alternatives.
There are two forms of CASE, simple and searched. In a simple CASE, a value at the top is compared to values listed for each alternative. In a searched CASE, each alternative has its own expression, which are evaluated independently as booleans.
-- simple: compare top <expression> to each WHEN <expression>
CASE <expression>
WHEN <expression> THEN ...
...
ELSE ...
END
-- searched: test each <expression>
CASE
WHEN <expression> THEN ...
...
ELSE ...
END
When used for a conditional condition, a searched expression is the simplest, and corresponds closely to an if-then-else. For the case in question:
CASE
WHEN @Closed THEN o.ClosedDate IS NOT NULL
ELSE o.ClosedDate IS NULL
END
If the test is more complex than a boolean variable, a searched expression makes even more sense to pick. The simple form may make sense if the test is a single value that you're comparing to, though you have to decide whether & how to handle values beyond what you expect. You could explicitly list all expected values, and handle other values as passing:
CASE @Closed
WHEN 1 THEN o.ClosedDate IS NOT NULL
WHEN 0 THEN o.ClosedDate IS NULL
ELSE 1
END
You could also handle unexpected values as failing, or cause an error.
You could handle just one value, treating all others as the alternative:
CASE @Closed
WHEN 1 THEN o.ClosedDate IS NOT NULL
ELSE o.ClosedDate IS NULL
END
-- or
CASE @Closed
WHEN 0 THEN o.ClosedDate IS NULL
ELSE o.ClosedDate IS NOT NULL
END
Which to pick depends on the business rules.
CASE expressions generally aren't sargable, especially as they often involve calculating values from columns (though you should always check the query execution plan to be sure).
Logical Operators
A logical consequence, if p then q (in logic, also written p implies q, or p ⇒ q) is logically equivalent to not p or q (in logic, also written ¬p ∨ q). There isn't a standard way of expressing the logical ternary expression, if p then q else r (in programming, p ? q : r) in terms of other logical operators, but there are two strong candidates:
(if p then q) and (if not p then r), (p ⇒ q) ∧ (¬p ⇒ q)(if p then q and not r) and (if not p then not q and r) (p ⇒ q∧¬r) ∧ (¬p ⇒ ¬q∧r)
Either of these could be the basis for rewriting if-then-else in terms of other logical operators:
-- interpretation 1
(NOT <test> OR <then condition>)
AND ( <test> OR <else condition>)
-- interpretation 2
(NOT <test> OR ( <then condition> AND NOT <else condition>))
AND ( <test> OR (NOT <then condition> AND <else condition>)
The result is less readable than IF() or CASE, but potentially has better performance.
For the case in question, since q and r are dependent on each other, only the 1st interpretation is worth consideration.
(@Closed OR o.ClosedDate IS NOT NULL) AND (NOT @Closed OR o.ClosedDate IS NULL)
Showing that this reduces to the appropriate conditions is left as an exercise.
Since this expression is a combination of simple comparisons, it's likely sargable (though, again, check the query execution plan).
IF and CASE Statements
In some contexts (such as stored procedures & functions), an IF or CASE statement can be used to pick among different queries. IF statements, like CASE and unlike IF functions, allow more than 2 alternatives (though that feature isn't needed here).
IF <test> THEN
SELECT ... WHERE <then condition> ...
ELSE
SELECT ... WHERE <else condition> ...
END IF
A CASE statement is different from a CASE operator; the two are distinguishable by:
- which grammatical contexts they occur in (roughly, the statement in procedures, the operator in queries),
- by what's allowed in the branches (roughly, the statement allows queries , the operator allows expressions), and
- the statement ends with
END CASE.
A CASE statement is quite similar to an IF statement, especially in the searched form:
CASE
WHEN <test> THEN
SELECT ... WHERE <then condition> ...
ELSE
SELECT ... WHERE <else condition> ...
END CASE
These statements are more verbose and involve repeating code (which is a chance to introduce errors), but are the most sargable option if performance is an issue.
With the sample in question:
IF @Closed THEN
SELECT *
FROM Orders o
WHERE o.OrderDate BETWEEN @startDate AND @endDate
AND o.ClosedDate IS NOT NULL;
ELSE
SELECT *
FROM Orders o
WHERE o.OrderDate BETWEEN @startDate AND @endDate
AND o.ClosedDate IS NULL;
END IF;
