はじめに
仕事の関係で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で抽出できます。
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つずつ括弧()でくくる事です。すなわち、次のように変更するのです。
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でも保存して実行ができます。
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でクエリーを記述できます。