対象者:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticsアナリティクスプラットフォームシステム(PDW)Microsoft FabricにおけるSQLデータベース
SQL Server で等号 (=) および等しくない (<>) 比較演算子が NULL 値と共に使用される場合の ISO 準拠の動作を指定します。
-
SET ANSI_NULLS ON-{expression} = NULLの値が{expression} <> NULL場合、Falseと{expression}の両方をNULLとして評価します。 この動作は ANSI に準拠しています。 -
SET ANSI_NULLS OFF-{expression} = NULLをTrueとして評価し、{expression} <> NULLの値がFalse場合は{expression}としてNULLします。NULL値は、=演算子と<>演算子を使用して比較する必要がないため、この動作はお勧めしません。
注
SET ANSI_NULLS OFF
ANSI_NULLS OFF データベース オプションは非推奨です。 SQL Server 2017 (14.x) 以降では、ANSI_NULLSは常に ON に設定されます。 非推奨の機能を新しいアプリケーションで使用しないでください。 詳細については、SQL Server 2017 のdeprecated データベース エンジン機能を参照してください。
構文
SQL Server、Azure Synapse Analytics のサーバーレス SQL プール、Microsoft Fabric の構文
SET ANSI_NULLS { ON | OFF }
Azure Synapse Analytics および Analytics Platform System (PDW) の構文
SET ANSI_NULLS ON
解説
ANSI_NULLSが ON の場合、SELECTを使用するWHERE column_name = NULL ステートメントは、column_nameに NULL 値がある場合でも 0 行を返します。
SELECTを使用するWHERE column_name <> NULL ステートメントは、column_nameに NULL 以外の値がある場合でも、0 行を返します。
ANSI_NULLSが OFF の場合、Equals (=) および Not Equal To (<>) 比較演算子は ISO 標準に従いません。
SELECTを使用するWHERE column_name = NULL ステートメントは、column_nameに null 値を持つ行を返します。
SELECTを使用するWHERE column_name <> NULL ステートメントは、列にNULL以外の値を持つ行を返します。 また、SELECTを使用するWHERE column_name <> XYZ_value ステートメントは、されていないすべての行を返します。
ANSI_NULLSが ON の場合、null 値に対するすべての比較は UNKNOWN と評価されます。
SET ANSI_NULLSが OFF の場合、データ値がNULLされている場合、null 値に対するすべてのデータの比較は TRUE に評価されます。
SET ANSI_NULLSを指定しない場合は、現在のデータベースの ANSI_NULLS オプションの設定が適用されます。
ANSI_NULLS データベース オプションの詳細については、ALTER DATABASE (Transact-SQL) を参照してください。
次の表は、null 値と null 以外の値を使用したブール式の結果に ANSI_NULLS の設定がどのように影響するかを示しています。
| ブール式 | ANSI_NULLSをオンに設定 | ANSI_NULLSをオフに設定 |
|---|---|---|
NULL = NULL |
不明 | true |
1 = NULL |
不明 | 偽 |
NULL <> NULL |
不明 | 偽 |
1 <> NULL |
不明 | true |
NULL > NULL |
不明 | 不明 |
1 > NULL |
不明 | 不明 |
NULL IS NULL |
true | true |
1 IS NULL |
偽 | 偽 |
NULL IS NOT NULL |
偽 | 偽 |
1 IS NOT NULL |
true | true |
SET ANSI_NULLS ON は、比較のオペランドの 1 つが NULL 変数またはリテラル NULLである場合にのみ、比較に影響します。 比較の両側が列または複合式の場合は、この設定は比較に影響しません。
ANSI_NULLS データベース オプションやSET ANSI_NULLSの設定に関係なく、スクリプトを意図したとおりに動作させるには、null 値を含む可能性がある比較でIS NULLとIS NOT NULLを使用します。
ANSI_NULLS 分散クエリを実行するには、ON に設定する必要があります。
ANSI_NULLS 計算列またはインデックス付きビューでインデックスを作成または変更する場合も、ON にする必要があります。 SET ANSI_NULLSが OFF の場合、計算列またはインデックス付きビューのインデックスを持つテーブルの CREATE、 UPDATE、 INSERT、および DELETE ステートメントは失敗します。 SQL Server では、要求された値に違反するすべての SET オプションを一覧表示するエラーが返されます。 また、 SELECT ステートメントを実行するときに、 SET ANSI_NULLS が OFF の場合、SQL Server は計算列またはビューのインデックス値を無視し、テーブルまたはビューにそのようなインデックスがないかのように選択操作を解決します。
注
ANSI_NULLS は、計算列またはインデックス付きビューのインデックスを処理するときに必要な値に設定する必要がある 7 つの SET オプションの 1 つです。 オプション ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、QUOTED_IDENTIFIER、および CONCAT_NULL_YIELDS_NULL も ON に設定し、NUMERIC_ROUNDABORT を OFF に設定する必要があります。
SQL Server Native Client ODBC ドライバーと SQL Server Native Client OLE DB Provider for SQL Server は、接続時に自動的に ANSI_NULLS を ON に設定します。 この設定は、ODBC データ ソースまたは ODBC 接続属性で構成でき、SQL Server のインスタンスに接続する前にアプリケーションの内部で設定される OLE DB 接続プロパティでも構成できます。
SET ANSI_NULLSの既定値は OFF です。
ANSI_DEFAULTSがオンの場合、ANSI_NULLSが有効になります。
ANSI_NULLSの設定は、解析時ではなく実行時に定義されます。
この設定の現在の設定を表示するには、次のクエリを実行します。
DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';
SELECT @ANSI_NULLS AS ANSI_NULLS;
アクセス許可
ロール public のメンバーシップが必要です。
例
次の例では、Equals (=) 比較演算子と Not Equal To (<>) 比較演算子を使用して、変数の NULL または 0 と null 値を比較します。
SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
IIF(@var = NULL, 'True', 'False') as EqualNull,
IIF(@var <> NULL, 'True', 'False') as DifferentNull,
IIF(@var = 0, 'True', 'False') as EqualZero,
IIF(@var <> 0, 'True', 'False') as DifferentZero
結果を次の表に示します。
| 等しい | 異なるヌル | イコールゼロ | ディファレンシャルゼロ |
|---|---|---|---|
| 正しい | いいえ | いいえ | 正しい |
SET ANSI_NULLS ONでは、NULLはこれらの演算子を使用してNULLまたは0と比較できないため、すべての式は 'False' として評価されます。
次の例では、= (等号) 比較演算子と <> (不等号) 比較演算子を使用して、テーブル内の NULL 値と NULL 以外の値を比較します。 この例では、 SET ANSI_NULLS 設定が IS NULLに影響しないことを示しています。
-- Create table t1 and insert values.
CREATE TABLE dbo.t1 (a INT NULL);
INSERT INTO dbo.t1 values (NULL),(0),(1);
GO
-- Print message and perform SELECT statements.
PRINT 'Testing default setting';
DECLARE @varname int;
SET @varname = NULL;
SELECT a
FROM t1
WHERE a = @varname;
SELECT a
FROM t1
WHERE a <> @varname;
SELECT a
FROM t1
WHERE a IS NULL;
GO
次に ANSI_NULLS を ON に設定し、テストします。
PRINT 'Testing ANSI_NULLS ON';
SET ANSI_NULLS ON;
GO
DECLARE @varname int;
SET @varname = NULL
SELECT a
FROM t1
WHERE a = @varname;
SELECT a
FROM t1
WHERE a <> @varname;
SELECT a
FROM t1
WHERE a IS NULL;
GO
次に ANSI_NULLS を OFF に設定し、テストします。
PRINT 'Testing ANSI_NULLS OFF';
SET ANSI_NULLS OFF;
GO
DECLARE @varname int;
SET @varname = NULL;
SELECT a
FROM t1
WHERE a = @varname;
SELECT a
FROM t1
WHERE a <> @varname;
SELECT a
FROM t1
WHERE a IS NULL;
GO
-- Drop table t1.
DROP TABLE dbo.t1;