Now, I wonder if I can achieve the same using plain SQL like a condition inside the WHERE same as within the SELECT
Yes.
and if so, is it optimum?
Nope.
Or programmatically is better and faster?
Yup.
Each expression in a WHERE clause is called a predicate. The more complex that expression is, the harder it is for the SQL engine to optimize for. Eventually it can hurt the sargability of the predicate. Sargability is the quality of how likely a predicate is applicable for an efficient index seek operation. A predicate that isn't sargable will likely result in a less performant operation to serve the data for the query.
Doing the more complex logic on the application side to construct a simpler predicate is typically better and more efficient, when possible.
As discussed in the comments, this would be the pseudo-code for how one could logically construct a similar predicate in pure SQL:
DECLARE @Variable INT = NULL; SELECT * FROM table WHERE ( @Variable IS NOT NULL -- Only if the variable has a value is when it'll be used to filter on AND column = @Variable ) OR column >= 0; -- Otherwise we fallback onto our default filter
The OR operator is what essentially hurts the sargability here. One could re-write this more efficiently in pure SQL using a UNION operator instead like so:
DECLARE @Variable INT = NULL; SELECT * FROM table WHERE @Variable IS NOT NULL -- Only if the variable has a value is when it'll be used to filter on AND column = @Variable UNION ALL SELECT * FROM table WHERE @Variable IS NULL AND column >= 0; -- Otherwise we fallback onto our default filter
At first glance, this may seem counterintuitive to query the same table twice, but the engine is typically smart enough to eliminate what doesn't apply. E.g. in this case, either the variable has a value or it doesn't, so only one side of the UNION ALL operator is processed.
FWIW, this pseudo-code is actually valid T-SQL syntax for Microsoft SQL Server (should you ever end up working in that system).
