SQL ServerとMicrosoft AccessにおけるSQLでの結合の書き方の違い

15446 ワード
SQLServer access SQLServer テキストリンク

はじめに

仕事の関係でSQL ServerとMicrosoft Accessを併用することになりました。どちらもSQLでテーブルのデータを取り扱えますが、やはり方言の関係でそっくりそのまま、同じSQLを使えるわけではありません。今回はテーブルを結合する時に気づいたSQLの方言についてとりあげます。今回の事例では内部結合を取り扱っていますが、外部結合でも同じです。

例題

例として次の3つのテーブルを内部結合する場合を考えてみましょう。

まず社員の名前と性別を定義した「社員」テーブルです。

社員名 性別
神島泰三 男
竜清康 男
辻雄太郎 男
桑野多美子 女
浅見令子 女
日下部孝介 男

次にこの会社は役職で報酬が決まっています。それを定義した「報酬」テーブルです。

役職 報酬
社員 5000
係長 6000
課長 7000
部長 8000
社長 10000

そして社員が役職に任命された履歴は「役職履歴」で定義されています。

社員名 任命年月日 役職
神島泰三 20170701 社員
竜清康 20170701 社員
竜清康 20180101 係長
辻雄太郎 20180101 社員
桑野多美子 20180701 係長
竜清康 20180701 課長
浅見令子 20180101 社員
桑野多美子 20190101 課長
神島泰三 20190101 係長
竜清康 20190101 部長
辻雄太郎 20190101 係長
日下部孝介 20170101 社員
日下部孝介 20170701 係長
日下部孝介 20180101 課長
日下部孝介 20180701 係長
日下部孝介 20190101 課長

この3つのテーブルを結合して、データを抽出することを考えてみます。例えば、2018年9月1日時点での女性社員の役職と報酬を調べるのです。

社員名 任命年月日 役職 報酬
浅見令子 20180101 社員 5000
桑野多美子 20180701 係長 6000

SQL Serverの場合

SQL Serverでは次のSQLで抽出できます。

SQLServerでのSQL
SELECT
   社員.社員名
  ,役職2.任命年月日
  ,役職2.役職
  ,報酬.報酬
FROM
  社員
  INNER JOIN
    (
    SELECT
       役職履歴.社員名
      ,Max(役職履歴.任命年月日) AS 直近任命年月日
    FROM
      役職履歴
    WHERE
      役職履歴.任命年月日<'20180901'
    GROUP BY
      役職履歴.社員名
    )  AS 役職
  ON
    社員.社員名 = 役職.社員名
    AND
    社員.性別 = '女'
  INNER JOIN
    役職履歴 AS 役職2
  ON
    役職2.社員名=役職.社員名
    AND
    役職2.任命年月日=役職.直近任命年月日
  INNER JOIN
    報酬
  ON
    報酬.役職=役職2.役職
;

ところが、これをAccessでは実行できません。クエリーを保存しようとすると次のエラーメッセージが表示されるのです。

Microsoft Accessの場合

結合を1つずつ括弧()でくくる

まず初めに行う事は結合を1つずつ括弧()でくくる事です。すなわち、次のように変更するのです。

結合を1つずつ括弧()でくくって修正したSQL
SELECT
   社員.社員名
  ,役職2.任命年月日
  ,役職2.役職
  ,報酬.報酬
FROM
  (
  (
  社員
  INNER JOIN
    (
    SELECT
       役職履歴.社員名
      ,Max(役職履歴.任命年月日) AS 直近任命年月日
    FROM
      役職履歴
    WHERE
      役職履歴.任命年月日<'20180901'
    GROUP BY
      役職履歴.社員名
    )  AS 役職
  ON
    社員.社員名 = 役職.社員名
    AND
    社員.性別 = '女'
  )
  INNER JOIN
    役職履歴 AS 役職2
  ON
    役職2.社員名=役職.社員名
    AND
    役職2.任命年月日=役職.直近任命年月日
  )
  INNER JOIN
    報酬
  ON
    報酬.役職=役職2.役職
;

ところが、これは不十分な修正です。保存しようとすると次のエラーメッセージが表示されます。ですのでもう一工夫必要です。

結合に関係ない抽出条件をWHERE句で指定する

さてここで Microsoft Access の画面をみてみましょう。

この色付けされている
社員.性別 = '女'
が問題の個所です。よくよく観ると、この条件は「社員」テーブルのみに関わる抽出条件で結合とは関係ありません。この部分をWHERE句で指定するのです。こうして修正したのが次のSQLです。今度はMicrosoft Accessでも保存して実行ができます。

MicrosoftAccessでのSQL
SELECT
   社員.社員名
  ,役職2.任命年月日
  ,役職2.役職
  ,報酬.報酬
FROM
  (
  (
  社員
  INNER JOIN
    (
    SELECT
       役職履歴.社員名
      ,Max(役職履歴.任命年月日) AS 直近任命年月日
    FROM
      役職履歴
    WHERE
      役職履歴.任命年月日<'20180901'
    GROUP BY
      役職履歴.社員名
    )  AS 役職
  ON
    社員.社員名 = 役職.社員名
  )
  INNER JOIN
    役職履歴 AS 役職2
  ON
    役職2.社員名=役職.社員名
    AND
    役職2.任命年月日=役職.直近任命年月日
  )
  INNER JOIN
    報酬
  ON
    報酬.役職=役職2.役職
WHERE
  社員.性別 = '女'
;

まとめ

以上のようにMicrosoft AccessのSQLで結合は次のことに注意する必要があります。

  • 結合を1つずつ括弧()でくくる
  • テーブルの結合とは関係のない抽出条件はWHERE句で指定する。

この2点に注意するればMicrosoft AccessでもSQLでクエリーを記述できます。