Oracleデータベースが構築されている環境に業務している方で、Where句に「(+)」と記述されたSQLを見かけたことはありますでしょうか。
Where句の「(+)」は、JOINを使った結合が推奨される前のOracleで使われていた「外部結合演算子」と呼ばれるもので、いわゆる「SQLのOracle方言」と呼ばれる古い構文なのですが、SQLに方言があることを知らない方にとっては未知の存在でしかなく、大いに戸惑ってしまうことでしょう。
今回はそういった方を対象にSQLの方言について紹介していきます。
目次
標準SQLと方言
それでは早速、SQLのOracle方言を紹介していきたいと思います。
標準的なSQLの記述方法を確認しながら、同じ結果を抽出できるOracle方言があるかどうか見ていきましょう。
標準SQLでの記述（外部結合）
まずはOracle方言の代表例である「外部結合」を標準SQLで記述する方法を見てみましょう。
基本的に以下のような記述になるかと思います。
|
1 2 3 4 5 |
SELECT (テーブル名.)カラム名 FROM テーブル1 LEFT OUTER JOIN テーブル2 ON テーブル2.カラム名 = テーブル1.カラム名 |
標準SQLでOUTER JOINを記述すると、WHERE句に結合条件を記述しなければエラーとなってしまい、結果を抽出することはできません。
ですが、Oracle方言では以下のようなSQLでデータ抽出が可能です。
|
1 2 3 4 5 |
SELECT (テーブル名.)カラム名 FROM テーブル1,テーブル2 WHERE テーブル1.カラム名 = テーブル2.カラム名(+) |
あらためて「OUTER JOIN」や「ON」を記述していないSQLを見ると、異質なSQLに見えてしまいますが、Oracleであれば実行可能ということについて覚えていただければと思います。
標準SQLでの記述（内部結合）
標準SQLでの内部結合の記述方法は以下の２パターンとなります。
ONで結合条件を記述するSQL
|
1 2 3 4 5 |
SELECT (テーブル名.)カラム名 FROM テーブル1 INNER JOIN テーブル2 ON テーブル2.カラム名 = テーブル1.カラム名 |
WHERE句で結合条件を記述するSQL
|
1 2 3 4 5 |
SELECT (テーブル名.)カラム名 FROM テーブル1,テーブル2 WHERE テーブル1.カラム名 = テーブル2.カラム名 |
「内部結合」というと前者の「INNER JOIN」を使うのが一般的かと思いますが、後者のSQLでも問題なくデータは抽出可能です。
後者のSQLに(+)をつけるだけで、Oracleでは外部結合になる、と把握しておいていただければと思います。
なお、後者の書き方でも実行可能ですので、内部結合に関しては、Oracle方言は存在しないと判断してよいでしょう。
外部結合演算子を使った結合
前述のとおり、SQLのWHERE句に(+)が記載されてあれば、Oracle方言の外部結合であるとみて間違いありません。
「JOIN」が使われるようになる前のOracle（8i）では、外部結合をする際「外部結合演算子(+)」を使って指定する方法がとられていました。
Oracleリファレンスによると、「外部結合演算子(+)」を使った記述方法を明確に「非推奨」としており、今後のバージョンアップで使われなくなることが想定できますが、実際の業務では、まだまだ「外部結合演算子(+)」を使ったSQLが数多く存在し、現実のデータベースで利用され続けています。
いずれかのタイミングでそれらのSQLを「JOIN」を使ったSQLに変換していく作業が必要となってくるかと思いますので、「外部結合演算子(+)」を使ったSQLがどのような抽出結果となるのかを把握しておく必要があるのは確かでしょう。
【プログラミング初心者必見】MySQLの基礎を解説します
SQLのOracle方言
「外部結合演算子(+)」を使ったSQLの詳細な解説については後述するとして、他にもSQLの方言がないか確認しておきましょう。
日付書式の違い
SQLで日付書式のデータを文字列として表示したい場合、標準SQLでは以下のような記述を用いることになりますが、OracleやMySQLでの記述方法に相違があります。
標準SQL（TIMESTAMP型）
|
1 |
FORMAT_TIMESTAMP(‘%Y-%m-%d %H:%M:%s’,’2021-01-02 09:00:00’) |
Oracle
|
1 |
TO_DATE('2021-01-02 09:00:00', 'YYYY-MM-DD HH24:MI:SS') |
MySQL
|
1 |
SR_TO_DATE(‘2020-01-02 09:00:00’,’%Y-%m-%d %H:%i:%s’) |
こういった違いもOracle方言として覚えておくとよいかもしれません。
NULLと空文字の扱い
標準SQLでは空文字を有効な値、NULLを不定な値として別の物として扱っているのに対し、Oracleでは空文字とNullを同一に扱っています。
Oracle方言というよりは「仕様の違い」と理解すべきかもしれませんが、ともかく標準SQLでは、nullは「列名 is null」でしかヒットしないのに対し、Oracleの場合は「列名=”」でもヒットしてしまいますので注意が必要です。
外部結合の構文
先述した通り、標準SQLの外部結合は「OUTER JOIN」を使い、ONで結合条件を記述しますが、Oracle方言では「外部結合演算子(+)」を使い、WHERE句で結合条件を記述します。
ただし、外部結合演算子(+)を右側の項目に付与するか左側の項目に付与するかで「LEFT OUTER JOIN」と「RIGHT OUTER JOIN」のどちらの意味になるかが異なりますので、こちらも把握しておくとよいでしょう。
右側に「外部結合演算子(+)」がある場合
変換前
|
1 |
WHERE テーブル1.カラム名 = テーブル2.カラム名(+) |
変換後
|
1 |
FROM テーブル1 LEFT OUTER JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名 |
左側に「外部結合演算子(+)」がある場合
変換前
|
1 |
WHERE テーブル1.カラム名(+) = テーブル2.カラム名 |
変換後
|
1 |
FROM テーブル1 RIGHT OUTER JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名 |
独自関数
Oracleに限ったわけではありませんが、標準SQLでは利用できない独自の関数が存在します。
DECODE、TO_MULTI_BYTE、TO_SINGLE_BYTE、SUBSTRB、INSTRBなどはOracle独自の関数となりますので注意が必要です。
以上が、標準SQLと方言SQLとの違いの代表的な例となります。
もちろん、ここで挙げた以外にも相違点は存在しますので、方言SQLを標準SQLに移植していくことを考えながら、方言SQLの使用は最小限に抑えるのが賢明かと思われます。
外部結合演算子の制限事項
最後に、先程言及した「外部結合演算子」の制限事項について紹介していきたいと思います。
「外部結合演算子」を使ったSQLを標準SQLに移植する際に必要となる知識ですので、じっくり時間をかけて理解していただければと思います。
- FROM句の結合構文を含む問合せには指定できない
FROM句の結合構文には結合演算子(+)を指定できません。 - 外部結合演算子を使用しない場合のエラーや警告がない
複数の結合条件によって結合される場合、すべての条件に外部結合演算子(+)を使用する必要があります。
外部結合演算子(+)を使用しない場合は全て内部結合に変換されてしまい、エラーや警告が出力されません。 - 複数の表からの（＋）は不可
1つの表は、複数の表から (+)を指定することができません。 - ORとの併用は不可
OR を使用した他の結合条件は使用できず、エラーとなってしまいます。 - サブクエリとの比較は不可
（+）演算子のついたカラムを副問い合わせで比較できません。 - IN演算子での指定は不可
IN 演算子に、（+）演算子のついたカラムを指定できません。 - 式には使用できない
(+) 演算子は列に指定する必要があり、式には使用できません。
MySQLとは？データベースについてプログラミング初心者向けに解説！
まとめ
いかがでしたか。
今回はOracleで使われる外部結合演算子を例に「SQLのOracle方言」を解説させていただきました。
これらの方言はOracle自体が非推奨としており、いずれ使われなくなる日が到来すると思われます。
ただ、今のところ古い構文で書かれたSQLは数多く残っており、今後、それらのSQLを新しい構文に書き直していく作業に従事することになるかもしれません。
そんな時、こちらの記事を思い出していただければ幸いです。
