Skip to content

is-distinct-from-transact-sql.md

Latest commit

 

History

History
198 lines (152 loc) · 6.53 KB

is-distinct-from-transact-sql.md

File metadata and controls

198 lines (152 loc) · 6.53 KB
title IS [NOT] DISTINCT FROM (Transact-SQL)
description Transact-SQL reference for the IS [NOT] DISTINCT FROM language element. Determine whether two expressions evaluate to NULL
author thesqlsith
ms.author derekw
ms.reviewer randolphwest
ms.date 07/25/2022
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
IS_[NOT]_DISTINCT_FROM_TSQL
IS [NOT] DISTINCT FROM
IS_NOT_DISTINCT_FROM_TSQL
IS NOT DISTINCT FROM
IS_DISTINCT_FROM_TSQL
IS DISTINCT FROM
helpviewer_keywords
IS [NOT] DISTINCT FROM predicate (Transact-SQL)
conditions [SQL Server], IS [NOT] DISTINCT FROM
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-ver16 || >=sql-server-linux-ver16 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

IS [NOT] DISTINCT FROM (Transact-SQL)

[!INCLUDE SQL Server 2022 Azure SQL Database Azure SQL Managed Instance FabricSE FabricDW FabricSQLDB]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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