SQL query optimiser for (ISNULL(@parameter) OR @parameter > Column) patterns and whether it optimises for when @parameter is null

If I had a form where the user can specify a bunch of filters (e.g. productid > 5, category = “Applicances”, Price > 3), it seems natural to make a single prepared statement like:

SELECT * 
FROM Products
WHERE 
  (@Price IS NULL OR Price > @Price) AND
  (@Category IS NULL OR @Category = Category) AND
  (@ProductID IS NULL OR Price > @Product);

Then add the @Price, @Category and @ProductID as parameters, which are null when the User hasn’t inputted anything into the form.

However, is the Query Optimiser clever enough for when @Category and @ProductID is null, to only use an Index for the Price? This is because you will have the condition (ISNULL(NULL) OR NULL = Category), which is always going to be true. E.g. does it do exactly the same thing as if:

SELECT * 
FROM Products 
WHERE Price > @Price

had been prepared?

Does it depend on which SQL engine is used? (SQL Server, MySQL, SQLite, Oracle, Postgres)

Answer

is the Query Optimiser clever enough for when @Category and @ProductID is null, to only use an Index for the Price?

Generally, combinations of and and or on multiple columns message up index usage. In some databases, it is possible to use multiple indexes for a single where clause.

However, it might be better to construct the query as dynamic SQL. Something like this in SQL Server (which is most similar to the code you have provided):

DECLARE @sql NVARCHAR(MAX) = '
SELECT * 
FROM Products
WHERE 1=1';

IF (@Price IS NOT NULL)
BEGIN
    SET @SQL = @SQL + ' Price > @Price'
END;

. . .

You can then pass the parameters in using sp_executesql.

Essentially, the code creates a family of queries — depending on the parameters — which can then each be optimized separately.