Answer.

Background of the Question

Modern BI and CRM systems often require the implementation of user-defined filters: it is important to flexibly combine arbitrary conditions (by date, gender, status, tags), as well as correctly intersect and combine them in SQL language. The resulting code should be readable, easily modifiable, and provide the expected result.

The Problem

Often, developers "on-the-fly" formulate WHERE with many AND/OR from user-defined filters, leading to logical errors and queries that are hard to maintain. This problem is exacerbated if filter values may be absent or combined with IN/EXISTS/LIKE.

Solution

For dynamic filters, a convenient pattern is "condition with OR is always TRUE if the value is not specified":

SELECT *
FROM users
WHERE (status = @status OR @status IS NULL)
  AND (gender = @gender OR @gender IS NULL)
  AND (created >= @from_date OR @from_date IS NULL)
  AND (city IN (@cities) OR @cities IS NULL);

Or using dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM users WHERE 1=1';
IF @status IS NOT NULL SET @sql += N' AND status = @status';
IF @gender IS NOT NULL SET @sql += N' AND gender = @gender';
-- ...
EXEC sp_executesql @sql, N'@status NVARCHAR(10),@gender NVARCHAR(10)', @status, @gender;

Key Features:

Correct intersection of filters with support for optional conditions
Good readability and extensibility
Absence of implicit "traps" with AND/OR logic

Tricky Questions.

Can WHERE 1=1 be used everywhere instead of checking for the presence of conditions?

No, 1=1 is only a starting condition for concatenating WHERE — it does not replace filters and is used solely for convenience in query generation.

What problems can arise if an empty list is included in IN ()?

IN (NULL) or IN () always returns false and yields an empty set, even in the absence of a filter. To work correctly, you should separately check for the emptiness of the list and exclude the condition altogether.

How will such constructs affect the use of indexes and performance?

If filters are implemented with OR or NULL checks, many DBMS may stop effectively using indexes, or the entire table may be scanned. It is advisable to apply approaches using dynamic SQL or filters with parameterized queries so that the planner can choose an index.

Typical Mistakes and Anti-Patterns

Accumulating OR in WHERE leads to scans of the entire table
Inserting conditions IN (@list) without checking that @list is not empty yields incorrect results
Using NULL instead of empty lists or default values

Real-life Example

Negative Case

A classic report with 10 filters, the code generates WHERE with many ORs — in the absence of several filters, the result returns an empty set or overly large scans, increasing the server load multiple times.

Pros:

Quick report implementation, minimal code Cons:
Unpredictable behavior and performance, difficulty in modification

Positive Case

A separate function generates filters only for specified parameters, and only for each group of conditions, a separate JOIN/FILTER is included. Where does not contain OR and unnecessary NULL checks.