14 Differences Between Standard SQL and Transact-SQL

前回の記事で、標準SQLとT-SQLの違い、誰がどちらを学ぶべきかを大まかに説明しました。 今回は、構文の違いに焦点を当て、その違いを例で説明したいと思います。 T-SQLは標準SQLの機能をすべて実装した拡張機能だと思っているならば、それは間違いです。 しかし、SQL Serverでは、標準SQLのほぼすべての機能を見つけることができます。 この記事では、標準SQLとTransact-SQLの構文の違いのいくつかの例を紹介します。

#1 データベースオブジェクトの名前

関係データベースシステムでは、テーブル、ビュー、列に名前を付けますが、時には同じ名前をキーワードとして使用したり特殊文字を使用しなければならないことがあります。 標準の SQL では、この種の名前は引用符 (“”) で囲みますが、T-SQL では、括弧 () で囲むこともできます。 T-SQL でのテーブル名のこれらの例を見てください。

CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100));CREATE TABLE dbo.test. ( Id INT, Name VARCHAR(100));

特殊名の最初の区切り記号（引用符）だけが標準 SQL の一部でもあります。

SELECTステートメントのどこが違うのですか？ どのように？ SELECT ステートメント単独で、式や、テーブルのカラムに由来しない他の値を使用することができます。 T-SQL では、以下の例のようになります。

SELECT 12/6 ;

この式では、12 ÷ 6 を評価するためのテーブルは必要ないので、FROM 文とテーブル名を省略することができます。

#3 結果セットのレコード数を制限する

標準SQLでは、以下に示す構文を使用して、結果のレコード数を制限することができます。 以下の例は、MS SQL Server の構文を示しています：

SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;

お気づきのように、これは ORDER BY 節を使用しています。 ORDER BY を使用せずに行を選択する別の方法は、T-SQL の TOP 節を使用することです。

SELECT TOP 10 * FROM tab;

#4 値の自動生成

標準 SQL では、自動的に生成される値を持つ列を作成することができます。 これを行う構文を以下に示します。

CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY);

T-SQL でも値を自動生成することができますが、次のようになります。 これらの数学関数の1つはCEIL(x)ですが、これはT-SQLには見当たりません。 代わりに、T-SQL は以下の非標準関数を提供します。 SIGN(x)、10進数値xを小数点以下の桁数に丸めるROUND(x,)、与えられた小数点以下の桁数に切り捨てるTRUNC(x)、値xの自然対数を返すLOG(x)、乱数を生成するRANDOM()です。 標準SQLではリストの最大値や最小値はMAX(list)やMIN(list)関数で返されますが、Transact-SQLではGREATEST(list)やLEAST(list)関数を使います。

T-SQL function ROUND:SELECT ROUND(col) FROM tab;

#6 集計関数

集約関数で別の構文の違いが見つかります。 COUNT、SUM、およびAVG関数はすべてカウントに関連する引数を取ります。 T-SQLでは、値が他の行と異なる場合にのみ行がカウントされるように、これらの引数値の前にDISTINCTを使用することができます。 標準SQLでは、これらの関数でDISTINCTを使用することはできません。

Standard SQL:SELECT COUNT(col) FROM tab;T-SQL:SELECT COUNT(col) FROM tab;SELECT COUNT(DISTINCT col) FROM tab;

しかし、T-SQLでは母集団共分散関数が見当たりません。

#7 日付と時刻の一部を取得する

ほとんどのリレーショナルデータベースシステムは、日付と時刻を操作するための多くの関数を提供します。

標準SQLでは、日付の一部を選択するEXTRACT(YEAR FROM x)関数や同様の関数は、YEAR(x)やDATEPART(year, x)といったT-SQL関数と異なっています。

また、現在の日時を取得する場合にも違いがあります。 標準SQLではCURRENT_DATE関数で現在の日付を取得できますが、MS SQL Serverでは同様の関数がないため、CAST関数の引数にGETDATE関数を使ってDATEデータ型に変換しなければなりません。

#8 文字列の操作

関数を使った文字列の操作も標準SQLとT-SQLで違います。 主な違いは、文字列から末尾と先頭のスペースを削除することにあります。 標準SQLではTRIM関数がありますが、T-SQLではいくつかの関連する関数があります。 TRIM（末尾と先頭のスペースを削除）、LTRIM（先頭のスペースを削除）、RTRIM（末尾のスペースを削除）です。

もう1つの非常によく使われる文字列関数はSUBSTRINGです。

SUBSTRING関数の標準SQL構文は以下のようになりますが、T-SQLでは、この関数の構文は以下のようになります：

SUBSTRING(str, start, length)

他の列や追加の文字列から値を追加する理由がある場合があります。 標準SQLでは、これを行うために次の構文を使用できます。

見てのとおり、この構文は、ある文字列を別の文字列に追加するために || 演算子を使用します。 SQL Server では、文字列のリストを連結する CONCAT 関数を使用することもできます。 標準SQLではこれを行うためにREPEAT(str, n)という関数が定義されています。 Transact-SQLはREPLICATE関数を提供しています。 例えば、

SELECT REPLICATE(str, x); 

ここで、xは文字列または文字を何回繰り返すかを示します。

#9 不等号演算子

SELECT文のレコードをフィルタするとき、時には不等号演算子を使用しなければならないことがあります。 標準SQLでは、この演算子として<>を定義していますが、T-SQLでは、標準演算子と!=演算子の両方を使用できます。 これは T-SQL 固有の関数であり、標準 SQL にはありません。

SELECT ISNULL(col1) FROM tab;

DML 構文のどの部分が異なるか？

T-SQL では、DELETE、UPDATE、および INSERT クエリーの基本構文は標準 SQL と同じですが、より高度なクエリーでは違いが出てきます。

#11 OUTPUTキーワード

OUTPUTキーワードはDELETE、UPDATE、およびINSERT文に出現します。 これは標準SQLでは定義されていません。

T-SQL を使用すると、クエリによって返される余分な情報を見ることができます。 UPDATEでは古い値と新しい値の両方、INSERTでは追加された値、DELETEでは削除された値が返されます。 この情報を見るには、INSERT、UPDATE、および DELETE で接頭辞を使用する必要があります。

UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col;

更新された列の以前の値と新しい値でレコードを変更した結果を見ることができます。 標準SQLでは、この機能はサポートされていません。

#12 INSERT INTO …の構文 SELECT

INSERTクエリのもう1つの構造は、INSERT INTO … SELECTです。 T-SQLでは、別のテーブルから目的のテーブルにデータを挿入することができます。 このクエリを見てください。

INSERT INTO tab SELECT col1,col2,... FROM tab_source;

これは標準機能ではなく、SQL Serverの特徴的な機能です。

#13 DELETEとUPDATEにおけるFROM句

SQL ServerではUPDATEとDELETE with FROM句の拡張構文が用意されています。 DELETE with FROM を使用すると、主キーと外部キーを参照して、あるテーブルの行を使用して、別のテーブルの対応する行を削除することができます。 同様に、UPDATE with FROM を使用すると、共通の値 (あるテーブルの主キーと別のテーブルの外部キー、たとえば同じ都市名) を使用して別のテーブルの行を参照し、あるテーブルから行を更新することができます。

DELETE FROM BookFROM AuthorWHERE Author.Id=Book.AuthorId AND Author.Name IS NULL;

UPDATE BookSET Book.Price=Book.Price*0.2FROM AuthorWHERE Book.AuthorId=Author.Id AND Author.Id=12;

標準SQLにはこの構文はありません。

#14 JOINによるINSERT、UPDATE、およびDELETE

別のテーブルに接続するために、JOINを使ってINSERT、UPDATE、およびDELETEも使用することができます。 この例は次のとおりです。

DELETE ItemOrder FROM ItemOrderJOIN Item ON ItemOrder.ItemId=Item.IdWHERE YEAR(Item.DeliveredDate) <= 2017;

この機能は標準SQLにはありません。

まとめ

この記事は、標準SQLとMS SQL Serverシステムを使用するT-SQLとの構文の違いに関するすべての問題を網羅しているわけではありません。 しかし、このガイドは Transact-SQL だけに特徴的ないくつかの基本的な機能と、MS SQL Server によって実装されていない SQL 標準構文について指摘するのに役立ちます。