Edit

IS [NOT] DISTINCT FROM (Transact-SQL)

  • Article

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

See also