We have pretty heavy logic in query that allows translating two-value logic expressions into SQL, which does three-value logic natively. Many popular relational databases besides SQL Server implement alternative patterns to perform equality comparisons with in-memory semantics, e.g. x == y in a LINQ expression could be translated to
| Translation |
Supported by |
x is y |
SQLite |
x <=> y |
MySQL and MariaDB |
decode(x, y, 0, 1) = 0 |
DB2 and Oracle |
EXISTS (SELECT x INTERSECT SELECT y) |
PostgreSQL, SQLite, SQL Server (as search condition) |
EXISTS (VALUES(x) INTERSECT VALUES(y)) |
DB2, PostgreSQL, SQL Server |
x IS NOT DISTINCT FROM y |
ANSI SQL:2003, PostgreSQL (apparently not sargable) |
This article presents an excellent survey of all the alternatives: http://modern-sql.com/feature/is-distinct-from.
For cases in which we know if either x or y are non-nullable, we can probably produce a more concise translation, but in more complex cases, the options above could lead to better SQL, even for SQL Server.
In any case, it should be possible for a provider to pick the best translation.
We have pretty heavy logic in query that allows translating two-value logic expressions into SQL, which does three-value logic natively. Many popular relational databases besides SQL Server implement alternative patterns to perform equality comparisons with in-memory semantics, e.g. x == y in a LINQ expression could be translated to
x is yx <=> ydecode(x, y, 0, 1) = 0EXISTS (SELECT x INTERSECT SELECT y)EXISTS (VALUES(x) INTERSECT VALUES(y))x IS NOT DISTINCT FROM yThis article presents an excellent survey of all the alternatives: http://modern-sql.com/feature/is-distinct-from.
For cases in which we know if either x or y are non-nullable, we can probably produce a more concise translation, but in more complex cases, the options above could lead to better SQL, even for SQL Server.
In any case, it should be possible for a provider to pick the best translation.