Appendix 2: Intersect Explained

It can be difficult to understand the examples in appendix 1, using an 'intersect' instead of an 'or' construction. In this appendix, the semantics of the intersect construction are explained, especially when used with variables.

Given the following declaration:

declare @i int = null, @j int = 1;

On the one hand, the expression “@i <> @j” should be ANSI safe, and on the other hand, the query performance should be good. If ANSI_NULLS is set to OFF, “null = 1” evaluates to TRUE. After all, the value “null” is not equal to the value “1”. However, if you set ANSI_NULLS to ON, every expression with an UNKNOWN value, evaluates to UNKNOWN. It is unknown what the value “null” represents. Consequently, it is unknown whether it equals “1” or not. As a result, different code paths can be taken. In an if statement, for example, the code will take the if branch when ANSI_NULLS is set to OFF, but the else branch if ANSI_NULLS is set to ON. The value UNKNOWN is not the same as the value TRUE, so the else branch will be chosen.

One way of making this code ANSI safe, is to explicitly test for null values.

if (@i <> @j) or (@i is null and @j is not null) or (@i is not null and @j is null)

However, the SQL Server optimizer cannot cope with 'or' statements well. Queries with “or” and table columns can give rise to bad query plans. In the example, there are two variables and no tables, so this problem will not arise. Nevertheless, the intersect construction will be explored in this simple example.

You can rewrite “@i <> @j” to “not (@i = @j)”. Then turn @i and @j into sets. As an integer variable, @i has the value “null” and @j has the value “1”. If you would turn the values into sets, this would be “{null}” and “{1}” (note the curly braces). You can turn a simple variable into a set by returning it as the result set of a select statement. So: “select @i” has the value “{null}” if , @i has the value “null”. In set theory, the question if the two integer values are the same, turns into the question if the two sets have elements in common.

If the two variables have the same value, the intersection of the two sets will contain an element: the intersection “{null} ∩ {null}” contains the element “{null}”, regardless of the value of the ANSI_NULLS setting. So even when ANSI_NULLS has the value ON, the intersection “{null} ∩ {null}” is not empty.

The question if two variables are equal, is the same question as: Does the intersection of the sets of their values contain an element? In addition, the question if two variables are different, is the same question as: Is the intersection of the sets of their values empty?

In SQL, you can use “exists” to check if a set contains an element and you can use “not exists” to check if a set is empty.