More than 3 years have passed since last update.

SQL Server SELECT COUNT(*);問題

Last updated at 2022-03-07Posted at 2022-03-06

前文

SELECT *;はエラーになる。FROM句が指定されてないから。当然の話。

1> SELECT *;
2> GO
メッセージ 263、レベル 16、状態 1、サーバー *******-*******、行 1
SELECT FROM にはテーブルを指定してください。

しかしSELECT COUNT(*);はエラーにならず、値が返ってくる。これって何？という素朴な疑問。

1> SELECT COUNT(*);
2> GO

-----------
          1

(1 行処理されました)

結論

内部的な動作として、1レコードを持った定数テーブルがFROM句に補完されている。

SELECT句の考察

SELECT句にはFROM句で指定したテーブル列以外の値、固定値等を含めることが許されている。

1. テーブル列 + テーブル列に依存しない値

1> SELECT
2>       'MSreplication_options'   [テーブル名]
3>     , SYSDATETIME()             [システム日時]
4>     , [optname]
5> FROM [master].[dbo].[MSreplication_options]
6> ;
7> GO
テーブル名              システム日時                            optname                                         
--------------------- -------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
MSreplication_options            2022-03-05 19:56:56.6692464 transactional                                              
MSreplication_options            2022-03-05 19:56:56.6692464 merge                                                      
MSreplication_options            2022-03-05 19:56:56.6692464 security_model                                             

(3 行処理されました)

2. テーブル列に依存しない値のみ

1> SELECT
2>       'MSreplication_options'   [テーブル名]
3>     , SYSDATETIME()             [システム日時]
4> FROM [master].[dbo].[MSreplication_options]
5> ;
6> GO
テーブル名              システム日時
--------------------- --------------------------------------
MSreplication_options            2022-03-05 19:57:28.9067131
MSreplication_options            2022-03-05 19:57:28.9067131
MSreplication_options            2022-03-05 19:57:28.9067131

(3 行処理されました)

この場合、SELECT句で指定した値が、FROM句で得られるレコード数分取得される。
従って、テーブルにレコードが存在しない場合や、WHERE句の絞り込み等でレコード数が 0件の場合は何も取得されない。

1> SELECT
2>       'MSreplication_options'   [テーブル名]
3>     , SYSDATETIME()             [システム日時]
4> FROM [master].[dbo].[MSreplication_options]
5> WHERE   1 <> 1
6> ;
7> GO
テーブル名              システム日時
--------------------- --------------------------------------

(0 行処理されました)

1> SELECT
2>       'MSreplication_options'   [テーブル名]
3>     , SYSDATETIME()             [システム日時]
4> FROM [master].[dbo].[spt_fallback_db]
5> ;
6> GO
テーブル名              システム日時
--------------------- --------------------------------------

(0 行処理されました)

3. `FROM`句が省略された場合

1レコードが取得される。

1> SELECT
2>       'MSreplication_options'   [テーブル名]
3>     , SYSDATETIME()             [システム日時]
4> ;
5> GO
テーブル名              システム日時
--------------------- --------------------------------------
MSreplication_options            2022-03-05 19:58:35.0423381

(1 行処理されました)

しかし、よくよく考えると3.の挙動は仕様として一貫性に欠けるとも言える。
FROM句が省略された以上、取得できるレコードは存在しない筈であり、何も取得できない、でなければ2.のテーブルにレコードが存在しない場合と整合性が取れない。
一方、それがユーザが期待する動きかと言えば違う。
SELECT SYSDATETIME();であればシステム日時、SELECT EXP(1);であればネイピア数が得られることを期待する。

1> SELECT SYSDATETIME();
2> SELECT EXP(1);
3> GO

--------------------------------------
           2022-03-05 20:16:13.1680703

(1 行処理されました)

------------------------
      2.7182818284590451

(1 行処理されました)

もしFROM句を省略したら何も取得できない仕様となると、それでも値を取得したい場合はわざわざダミーのテーブルを指定する必要がでてくる。それでは使い勝手が悪い。

1> SELECT
2>       'テーブル指定なし' [テーブル名]
3>     , SYSDATETIME()    [システム日時]
4> FROM (VALUES (NULL))Dummy(d)
5> ;
6> GO
テーブル名         システム日時
---------------- --------------------------------------
テーブル指定なし              2022-03-05 20:17:28.6255227

(1 行処理されました)

そこでFROM句が省略されても固定値等、テーブル列以外の値だけが指定された場合に限り、取得できる仕様になったと考えられる。
またその場合、複数レコードが返ってきても無駄なので、1レコードだけ返ってくると。
そしてその仕様を実現する為には、FROM句が省略された場合は 1レコードだけ持った仮想的なテーブルを指すようにする必要がある。

例えばFROM句を省略してROW_NUMBER関数を使用すると以下の結果が得られる。

1> SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) [連番];
2> GO    
連番
--------------------
                   1

(1 行処理されました)

ROW_NUMBER関数とは1始まりの連番を行セットに設定する仕様であり、1レコードだけ存在するので1が返ってくる、と考えると辻褄が合う。
そう考えると、SELECT COUNT(*);が返す1も、単にそのレコード数が返ってきているだけ、と考えると合点がいく。

実行計画

長々と書いてきたが、実行計画を見れば分かる。

「定数の内部テーブル」なるものが補完されていることが確認できる。つまりFROM句が省略された場合、実行計画として定数テーブルが追加される仕様であることが分かる。これを定数テーブル補完計画という（嘘）。
公式の説明は以下。

プラン表示の論理操作と物理操作のリファレンス - SQL Server

Constant Scan 操作は、1 つ以上の定数行をクエリに導入します。多くの場合、 Compute Scalar 操作は、 Constant Scan 操作によって生成された行に列を追加するために、 Constant Scan の後で使用されます。

しかし、この件について解説言及しているサイトって皆無なんだよな。かろうじて見つけたのが以下。
Constant Scan in Execution Plans
Sql server's Constant scan - clarification_ - Stack Overflow

Oracle

OracleではFROM句の省略が不可でDUALを指定する必要がある。
FROM DUALが決まり文句なっているせいかたまにステートメントか何かと勘違いしている人がいるが、実態は1レコードだけ持ったシステムテーブル。

Oracle

SQL> DESC DUAL;
 名前                                                                                             NULL?    型
 ----------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------
 DUMMY                                                                                                    VARCHAR2(1)

Oracle

SQL> SELECT COUNT(*) FROM DUAL;

  COUNT(*)
----------
         1

Oracle

SQL> SELECT ROW_NUMBER() OVER(ORDER BY NULL) FROM DUAL;
  2    3    4  
ROW_NUMBER()OVER(ORDERBYNULL)
-----------------------------
                            1

SQL ServerはFROM句が省略できる、といっても、実は内部的な実装はOracleと同じと言える。
Oracleの仕様はFROM句の指定が不要な場合でも、1レコードだけ持ったダミーテーブルを指定することが合理的であることを示している。
それと（PostgreSQLぐらいしか確認していないが）FROM句の省略が許されている他の DB も同様の仕様と思われる。

公式情報

SELECT COUNT(*);が1を返す状況は初見ではかなり不自然に感じるが、内部的な動きが見えてくると納得できる。
しかし公式サイトにはここら辺の詳細な説明は無いんだよな。せいぜい以下。

FROM 句と JOIN、APPLY、PIVOT (T-SQL) - SQL Server

FROM 句は通常、SELECT ステートメントで必要です。例外は、テーブル列がリストアップされず、リストアップされる唯一の項目がリテラルか、変数か、数式の時です。

そもそも、その「例外」の場合は 1レコードだけ生成される旨も明言されていないし。既成事実としてそれが当たり前になっているが。
しかし、実テーブルの指定が必須ではないという仕様は、クエリをスクリプト言語的な使い方を可能としている。フィボナッチ数列を求めるクエリとか。
何故わざわざクエリで、と思われるかもしれないが、数列はレコードとも考えることができるので、案外相性がいいんだよね。

SQL Server SELECT COUNT(*);問題

前文

結論

SELECT句の考察

1. テーブル列 + テーブル列に依存しない値

2. テーブル列に依存しない値のみ

3. `FROM`句が省略された場合

実行計画

Oracle

公式情報

関連投稿

2022/03/07 追記

SQL Server SELECT COUNT(*);問題

前文

結論

SELECT句の考察

1. テーブル列 + テーブル列に依存しない値

2. テーブル列に依存しない値のみ

3. FROM句が省略された場合

実行計画

Oracle

公式情報

関連投稿

2022/03/07 追記

3. `FROM`句が省略された場合