CODEWITHSUNDEEP
sqlsql-servert-sql
cost
cost

Reputation: 4480

Why does this SQL statement work the way it does?

I've been looking to find a good way to do a SQL query where I can have a where statement which, if left blank, will act like it wasn't there at all. I've found this, which seems to work quite well:

WHERE (Column = @value OR @value is null)

If I specify a value for @value, the search is filtered like I want. But if I pass in null, it's like saying @value can be anything. I like this. This is good. What I don't understand though is, why does this work like this?

Upvotes: 1

Views: 138

Answers (2)

Michael Petrotta
Michael Petrotta

Reputation: 60902

If @value is null, your WHERE clause:

WHERE (Column = @value OR @value is null)

reduces to

WHERE (Column = @value OR 1=1)

(This is similar to if (Column == value || true) in other common languages)

An OR conjunction is true if either of its operands (sides) are true: SQL uses three valued logic

+---------+------+---------+---------+
| A OR B  | TRUE | Unknown |  FALSE  |
+---------+------+---------+---------+
| TRUE    | TRUE | TRUE    | TRUE    |
| Unknown | TRUE | Unknown | Unknown |
| FALSE   | TRUE | Unknown | FALSE   |
+---------+------+---------+---------+

And so:

  • If @value is null, the right side of your WHERE clause is true, so the entire conditional is true, and your WHERE clause will always be satisfied.
  • If @value isn't null, then the right side of your WHERE clause is false, then whether the WHERE clause is satisfied depends on whether Column = @value.

Upvotes: 3

Thilo
Thilo

Reputation: 262534

Well, there are two cases:

1) @value is "something".

In this case, the second clause is always false, because "something" is never null. So all that effectively remains is WHERE Column = @value.

2) @value is null

In this case, the second clause is always false, because null never equals anything. So all that effectively remains is WHERE @value is null and @value is known to be null, so this is like WHERE 1 = 1 and the whole WHERE is ignored. The database should be clever enough to figure this out before touching any data, so this should perform just like if there was no condition specified at all.

So what you have here, is a single SQL statement that can act like two, with an "optional WHERE". The advantage over two separate SQL statements for the two cases is that you don't need conditional logic in your application when building the SQL statement (which can get really hairy if there are more than one of these "toggles").

Upvotes: 1

Related Questions