1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

SQL Server SELECT COUNT(*);問題

Last updated at Posted 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 ServerFROM句が省略できる、といっても、実は内部的な実装はOracleと同じと言える。
Oracleの仕様はFROM句の指定が不要な場合でも、1レコードだけ持ったダミーテーブルを指定することが合理的であることを示している。
それと(PostgreSQLぐらいしか確認していないが)FROM句の省略が許されている他の DB も同様の仕様と思われる。

公式情報

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

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

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

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

関連投稿

ORDER BY (SELECT NULL) ~レコード取得順序の考察~ - Qiita

2022/03/07 追記

改めてググったら、Stack Overflow で既出ネタだった……_| ̄|○。
sql - SELECT COUNT(_) ; - Stack Overflow
実はこのネタ、2年程前にもちらっと取り上げたことがあり、その時は特に有力な情報が見つからなかったので油断していたよ。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?