ある条件でJOINできない場合、別の条件でJOINしたい

Question

実現したいこと

データの都合上、2つのテーブルをLEFT JOINした際にNULLになる場合、別の条件で計算した値を挿入したい

前提

以下の2つのテーブルが存在します。

GasAmount
|日付|県|単価|
|:--:|--:|
|2020/4/1|A|120|
|2020/4/1|B|122|
|2020/4/1|C|124|
|2020/5/1|A|121|
|2020/5/1|B|123|
|2020/5/1|C|125|
|2020/6/1|A|122|
|2020/6/1|B|124|
|2020/6/1|C|126|

GasAmountは月別のガソリン単価を示します。

Distance

|従業員|日付|県|距離|
|:--:|:--:|:--:|
|a|2020/4/1|A|1|
|b|2020/4/1|B|2|
|c|2020/4/1|E|3|
|a|2020/5/1|B|4|
|b|2020/5/1|A|5|
|c|2020/5/1|E|6|
|a|2020/6/1|A|7|
|b|2020/6/1|B|8|
|c|2020/6/1|C|9|

Distanceは各従業員が毎月どの県で何ｋｍ走っているかを示します。

これらを組み合わせ、各従業員がいつ、どれだけ運賃=単価*距離を貰えるか計算します。

発生している問題・エラーメッセージ

SELECT [Distance].*, [GasAmount].[単価]
FROM [Distance] 
LEFT JOIN [GasAmount] ON 
[Distance].[県] = [GasAmount].[県] 
AND [Distance].[日付] = [GasAmount].[日付]

これにより、テーブルを組み合わせることができます。

|従業員|日付|県|距離|単価|
|:--:|:--:|:--:|:--:|
|a|2020/4/1|A|1|120|
|b|2020/4/1|B|2|122|
|c|2020/4/1|E|3||
|a|2020/5/1|B|4|123|
|b|2020/5/1|A|5|121|
|c|2020/5/1|E|6||
|a|2020/6/1|A|7|122|
|b|2020/6/1|B|8|124|
|c|2020/6/1|C|9|126|

しかし、データの都合上、[Distance].[県]には[GasAmount].[県]に存在しないデータが入ることがあり、その場合、その日付の県Aの単価を入れたいです。
その場合のSQLコードを教えてください。

補足情報（FW/ツールのバージョンなど）

SQL Server 2017

Comments

[Orlofsky]

>SELECT [Distance].* [GasAmount].[単価]
↓
SELECT [Distance].[距離] * [GasAmount].[単価]
では？

[woria]

SQLを修正しました。

[Orlofsky]

質問のテーブル定義はCREATE TABLEに、テーブル中のデータはINSERTに訂正できたほうがSQLを実行できるので的確なコメントが付き易いかと。

Answer 1

予めと特定の県の部分も結合しておき、取得できない場合で差し替える

SELECT [Distance].*
     , COALESCE([GasAmount].[単価], GasAmount2.単価) as 単価
FROM  [Distance] LEFT JOIN [GasAmount] 
      ON  [Distance].[県] = [GasAmount].[県] 
      AND [Distance].[日付] = [GasAmount].[日付]
      LEFT JOIN [GasAmount] GasAmount2
      ON  [Distance].[日付] = [GasAmount2].[日付]
      AND GasAmount2.県='A'

以下はサブクエリー版。

SELECT [Distance].*
     , COALESCE([GasAmount].[単価]
         , (select 単価 from GasAmount where 日付=Distance.日付 and 県='A')
      ) as 単価
FROM  [Distance] LEFT JOIN [GasAmount] 
      ON  [Distance].[県] = [GasAmount].[県] 
      AND [Distance].[日付] = [GasAmount].[日付]

どちらが性能が良いかは実行計画で確認して下さい。

Comments

[woria]

COALESCEを初めて知りました。ありがとうございます！

[sazi]

条件によって取得先をスイッチするような場合、SQLでの考え方としては、From句で予めそれらの条件のデータを揃えてから、selectでcase式などで切り替えるように考えると良いですよ。

[sousuke]

COALESCE内でサブクエリselectすると確か要素分だけselect回るんでオススメできません。isnullの方がselect1回で済むのでいいです。

[sazi]

>@sousukeさん
相関副問合せは当然駆動表のデータ数分問合せされるのが基本ですけど
COALESCEとisNullでそのサブクエリーに関する実行計画が変わるって事ですか？

[sousuke]

https://docs.microsoft.com/ja-jp/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

ここによれば私の解釈ではそう書いてあるようにみえます。

[sazi]

case の式は真偽に関わらず、必ず評価されるってやつですね。
実行計画にまで反映されるかどうか分かりませんけど、確かにサブクエリーの場合はIsNullの方が良さそうですね。

[sousuke]

『たとえば、COALESCE((subquery), 1) というコードを実行すると、サブクエリは 2 回評価されます。』
とあるので使うとしても第一案のように先に値を解決したり変数に一回受けてから使うように私はしています。isnullからcoalesceにする場合も往々にしてあるので、isnullするときでも結局のところ直でサブクエリは書きませんね…

[sazi]

サブクエリーのパターンも回答したのは、母数が小さければサブクエリーであっても高速な場合があるから、取捨選択の為に回答しています。