IS [NOT] DISTINCT FROM (Transact-SQL)
Applies to: 
SQL Server 2022 (16.x)
Azure SQL Database
Azure SQL Managed Instance
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL.
IS [NOT] DISTINCT FROM is a predicate used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.
Transact-SQL syntax conventions
Syntax
expression IS [NOT] DISTINCT FROM expression
Arguments
expression
Any valid expression.
The expression can be a column, a constant, a function, a variable, a scalar subquery, or any combination of column names, constants, and functions connected by an operator or operators, or a subquery.
Remarks
Comparing a NULL value to any other value, including another NULL, will have an unknown result. IS [NOT] DISTINCT FROM will always return true or false, as it will treat NULL values as known values when used as a comparison operator.
The following sample table uses values A and B to illustrate the behavior of IS [NOT] DISTINCT FROM:
| A | B | A = B | A IS NOT DISTINCT FROM B |
|---|---|---|---|
| 0 | 0 | True | True |
| 0 | 1 | False | False |
| 0 | NULL | Unknown | False |
| NULL | NULL | Unknown | True |
When executing a query that contains IS [NOT] DISTINCT FROM against linked servers, the query text sent to the linked server will vary, based on whether we can determine that the linked server has the capability to parse the syntax.
If we determine that the linked server can parse IS [NOT] DISTINCT FROM, we will decode the syntax as-is. If we can't determine that a linked server can parse IS [NOT] DISTINCT FROM, we will decode to the following expressions:
A IS DISTINCT FROM B will decode to: ((A <> B OR A IS NULL OR B IS NULL) AND NOT (A IS NULL AND B IS NULL))
A IS NOT DISTINCT FROM B will decode to: (NOT (A <> B OR A IS NULL OR B IS NULL) OR (A IS NULL AND B IS NULL))
Examples
A. Use IS DISTINCT FROM
The following example returns rows where the id field is distinct from the integer value of 17.
DROP TABLE IF EXISTS #SampleTempTable;
GO
CREATE TABLE #SampleTempTable (id INT, message nvarchar(50));
INSERT INTO #SampleTempTable VALUES (null, 'hello') ;
INSERT INTO #SampleTempTable VALUES (10, null);
INSERT INTO #SampleTempTable VALUES (17, 'abc');
INSERT INTO #SampleTempTable VALUES (17, 'yes');
INSERT INTO #SampleTempTable VALUES (null, null);
GO
SELECT * FROM #SampleTempTable WHERE id IS DISTINCT FROM 17;
DROP TABLE IF EXISTS #SampleTempTable;
GO
The results exclude all rows where id matched the value of 17.
id message
----------- ---------
NULL hello
10 NULL
NULL NULL
B. Use IS NOT DISTINCT FROM
The following example returns rows where the id field isn't distinct from the integer value of 17.
DROP TABLE IF EXISTS #SampleTempTable;
GO
CREATE TABLE #SampleTempTable (id INT, message nvarchar(50));
INSERT INTO #SampleTempTable VALUES (null, 'hello') ;
INSERT INTO #SampleTempTable VALUES (10, null);
INSERT INTO #SampleTempTable VALUES (17, 'abc');
INSERT INTO #SampleTempTable VALUES (17, 'yes');
INSERT INTO #SampleTempTable VALUES (null, null);
GO
SELECT * FROM #SampleTempTable WHERE id IS NOT DISTINCT FROM 17;
DROP TABLE IF EXISTS #SampleTempTable;
GO
The results return only the rows where the id matched the value of 17.
id message
----------- --------
17 abc
17 yes
C. Use IS DISTINCT FROM against a NULL value
The following example returns rows where the id field is distinct from NULL.
DROP TABLE IF EXISTS #SampleTempTable;
GO
CREATE TABLE #SampleTempTable (id INT, message nvarchar(50));
INSERT INTO #SampleTempTable VALUES (null, 'hello') ;
INSERT INTO #SampleTempTable VALUES (10, null);
INSERT INTO #SampleTempTable VALUES (17, 'abc');
INSERT INTO #SampleTempTable VALUES (17, 'yes');
INSERT INTO #SampleTempTable VALUES (null, null);
GO
SELECT * FROM #SampleTempTable WHERE id IS DISTINCT FROM NULL;
DROP TABLE IF EXISTS #SampleTempTable;
GO
The results return only the rows where the id wasn't NULL.
id message
----------- --------
10 NULL
17 abc
17 yes
D. Use IS NOT DISTINCT FROM against a NULL value
The following example returns rows where the id field isn't distinct from NULL.
DROP TABLE IF EXISTS #SampleTempTable;
GO
CREATE TABLE #SampleTempTable (id INT, message nvarchar(50));
INSERT INTO #SampleTempTable VALUES (null, 'hello') ;
INSERT INTO #SampleTempTable VALUES (10, null);
INSERT INTO #SampleTempTable VALUES (17, 'abc');
INSERT INTO #SampleTempTable VALUES (17, 'yes');
INSERT INTO #SampleTempTable VALUES (null, null);
GO
SELECT * FROM #SampleTempTable WHERE id IS NOT DISTINCT FROM NULL;
DROP TABLE IF EXISTS #SampleTempTable;
GO
The results return only the rows where the id was NULL.
id message
----------- --------
NULL hello
NULL NULL