Nullable Expressions (M to SQL Mapping)

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

The == and != operators in Microsoft code name “M” have the semantic that null == null and null != 1. In SQL, both of these return unknown, a third truth value that is not supported in “M”. To get the “M” semantics, when one side of an operator is a type that can be null (for example, F(a : Integer32?, b : Integer32?) { a == b }), a special case logic is generated that checks for null values to ensure that null == null and null != 1 result in true.

This applies to all supported scalar types. Equality comparison of entities is not supported, so nullable entity comparison is unsupported as well.

Nullable comparison operators (<, >, <=, >=) that operate on nullable integers actually return Logical?: 1 <= 2 is true, 2 <= 1 is false, null <= null is null, and 1 <= null is null. This applies to all supported numeric types.

Operator	“M” Example	SQL Example
Nullable ==	A == B	(A is null and B is null) or (A is not null and B is not null and A = B)
Nullable !=	A != B	(A is null and B is not null) or (A is not null and B is null) or (A is not null and B is not null and A <> B)
Nullable Comparison	A < B A > B A <= B A >= B	case when A is null or B is null then null when A <comparison op> B then 1 else 0 end