作成日:  (更新日: )

RDBMSのSQLの比較

このページは、RDBMSのSQLの文法などの違いについてまとめる予定のページです。

目次

注意

  • このページを作ったひとが自分のために記載しているため網羅性はありません。
  • データ型は「RDBMS のデータ型の比較」にあります。

一覧

バージョン確認

RDBMS 例
MySQL SELECT VERSION();
PostgreSQL SELECT VERSION();
Oracle SELECT * FROM V$VERSION;
SQL Server SELECT @@VERSION;
SQLite SELECT sqlite_version();

識別子のエスケープ

RDBMS 例
MySQL
　ANSI_QUOTES モードなし `keyword`
　ANSI_QUOTES モードあり `keyword`, "keyword"
PostgreSQL "keyword"
Oracle "keyword"
SQL Server
　SET QUOTED_IDENTIFIER OFF [keyword]
　SET QUOTED_IDENTIFIER ON [keyword], "keyword"
SQLite "keyword", [keyword], `keyword`

文字列

リテラル

RDBMS 例
MySQL
　ANSI_QUOTES モードなし 'text', "text"
　ANSI_QUOTES モードあり 'text'
PostgreSQL 'text'
Oracle 'text'
SQL Server
　SET QUOTED_IDENTIFIER OFF 'text', "text"
　SET QUOTED_IDENTIFIER ON 'text'
SQLite 'text'

引用符のエスケープ

RDBMS 例
MySQL
　NO_BACKSLASH_ESCAPES モードなし 'text '' text', 'text \' text'
　NO_BACKSLASH_ESCAPES モードあり 'text '' text'
PostgreSQL
　standard_conforming_strings = off 'text '' text', 'text \' text', E'text \' text'
　standard_conforming_strings = on 'text '' text', E'text \' text'
Oracle 'text '' text', q'{text '' text}' など (代替引用符)
SQL Server 'text '' text'
SQLite 'text '' text'

文字列結合 (演算子)

RDBMS 例
MySQL
　PIPES_AS_CONCAT モードなし 演算子なし (CONCAT('A', 'B') など)
　PIPES_AS_CONCAT モードあり 'A' || 'B'
PostgreSQL 'A' || 'B'
Oracle 'A' || 'B'
SQL Server 'A' + 'B'
SQLite 'A' || 'B'

ゼロ埋め

RDBMS 例 (1 を 000001 (6桁) にする)
MySQL LPAD(1, 6, '0'), RIGHT(CONCAT('000000', 1), 6)
PostgreSQL TO_CHAR(1, 'FM000000'), LPAD(1::text, 6, '0'), RIGHT('000000' || 1, 6)
Oracle TO_CHAR(1, 'FM000000'), LPAD(1, 6, '0'), SUBSTR('000000' || 1, -6)
SQL Server RIGHT(CONCAT('000000', 1), 6)
SQLite SUBSTR('000000' || 1, -6)

日時

タイムゾーンのセット (セッション)

RDBMS 例
MySQL SET @@session.time_zone = 'Asia/Tokyo';
PostgreSQL SET SESSION TIME ZONE 'Asia/Tokyo';
Oracle ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';
SQL Server -
SQLite -

タイムゾーンの確認 (セッション)

RDBMS 例
MySQL SELECT @@session.time_zone;
PostgreSQL SHOW TIME ZONE
Oracle SELECT SESSIONTIMEZONE FROM dual;
SQL Server SELECT CURRENT_TIMEZONE();
SQLite -

現在時刻

タイムゾーン無し (セッションタイムゾーンの時刻 + ミリ秒なし)
RDBMS 例
MySQL NOW(), LOCALTIMESTAMP, CURRENT_TIMESTAMP
PostgreSQL LOCALTIMESTAMP(0)
Oracle LOCALTIMESTAMP(0)
SQL Server DATEADD(millisecond, -DATEPART(millisecond, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) (ミリ秒切り捨て)
CONVERT(datetime2(0), CURRENT_TIMESTAMP) (ミリ秒四捨五入)
SQLite datetime('now', 'localtime')
タイムゾーンあり (セッションタイムゾーンの時刻)
RDBMS 例
MySQL -
PostgreSQL CURRENT_TIMESTAMP (timestamp with time zone)
Oracle CURRENT_TIMESTAMP (timestamp with time zone)
SQL Server SYSDATETIMEOFFSET() (datetimeoffset(7))
SQLite -

書式化

RDBMS 例 (YYYY/MM/DD HH:MM:SS にする)
MySQL DATE_FORMAT(LOCALTIMESTAMP, '%Y/%m/%d %H:%i:%s')
PostgreSQL TO_CHAR(LOCALTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS')
Oracle TO_CHAR(LOCALTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS')
SQL Server FORMAT(CURRENT_TIMESTAMP, 'yyyy/MM/dd HH:mm:ss')
SQLite strftime('%Y/%m/%d %H:%M:%S', datetime('now', 'localtime'))

キャスト

RDBMS 例
MySQL CAST(式 AS 型名), CONVERT(式, 型名)
PostgreSQL CAST(式 AS 型名), 式::型名
Oracle CAST(式 AS 型名)
SQL Server CAST(式 AS 型名), CONVERT(式, 型名)
SQLite CAST(式 AS 型名)

CREATE TABLE

自動採番列の指定

RDBMS 例
MySQL AUTO_INCREMENT
SERIAL 型エイリアス (BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアス)
PostgreSQL GENERATED AS IDENTITY
SERIAL 型エイリアス (integer NOT NULL DEFAULT nextval('固有シーケンス') のエイリアス)
Oracle (12c～) GENERATED AS IDENTITY
SQL Server IDENTITY(1, 1)
SQLite AUTOINCREMENT
MySQL の例
CREATE TABLE test1 (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE test2 (
  id SERIAL PRIMARY KEY
);
PostgreSQL の例
CREATE TABLE test1 (
  id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

CREATE TABLE test2 (
  id SERIAL PRIMARY KEY
);
Oracle の例
CREATE TABLE test1 (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 
);
SQL Server の例
CREATE TABLE test1 (
  id INT IDENTITY(1,1) PRIMARY KEY 
);
SQLite の例
CREATE TABLE test1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT
);

コメント (列)

RDBMS 例
MySQL CREATE TABLE 内で 列名 列定義 COMMENT 'コメント'
PostgreSQL COMMENT ON COLUMN テーブル名.列名 IS 'コメント'
Oracle COMMENT ON COLUMN テーブル名.列名 IS 'コメント'
SQL Server EXEC sp_addextendedproperty
SQLite -
SQL Server の例
EXEC sp_addextendedproperty
@name = N'MS_Description',
@value = 'コメント',
@level0type = N'Schema', @level0name = 'スキーマ名',
@level1type = N'Table',  @level1name = 'テーブル名',
@level2type = N'Column', @level2name = '列名';

既存テーブルから生成

RDBMS 例
MySQL CREATE TABLE 新規テーブル AS SELECT * FROM 既存テーブル
PostgreSQL CREATE TABLE 新規テーブル AS SELECT * FROM 既存テーブル
Oracle CREATE TABLE 新規テーブル AS SELECT * FROM 既存テーブル
SQL Server SELECT * INTO 新規テーブル FROM 既存テーブル
SQLite CREATE TABLE 新規テーブル AS SELECT * FROM 既存テーブル

SELECT

結果の制限 (LIMIT, FETCH)

RDBMS 例
MySQL LIMIT 10 OFFSET 20
PostgreSQL LIMIT 10 OFFSET 20, OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Oracle OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
SQL Server (2012～) OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
SQLite LIMIT 10 OFFSET 20

OVER句 (ウィンドウ関数)

RDBMS 例
MySQL (8.0～) SELECT gender, ROW_NUMBER() OVER(PARTITION BY gender ORDER BY id ASC) AS num FROM emp
PostgreSQL 〃
Oracle 〃
SQL Server 〃
SQLite (3.25～) 〃

グループ化したデータの文字列化 (カンマ区切りなど)

RDBMS 例
MySQL GROUP_CONCAT()
PostgreSQL string_agg()
Oracle XMLAGG()
SQL Server FOR XML PATH (''), STRING_AGG() (2017～)
SQLite GROUP_CONCAT()
サンプルデータ
CREATE TABLE emp ( id INTEGER, emp_name CHARACTER VARYING(10), gender INTEGER );
INSERT INTO emp VALUES (1, 'emp1', 1);
INSERT INTO emp VALUES (2, 'emp2', 1);
INSERT INTO emp VALUES (3, 'emp3', 2);
INSERT INTO emp VALUES (4, 'emp4', 2);
期待する結果
gender	emp_names
1	emp1,emp2
2	emp3,emp4
MySQL の例
SELECT gender, GROUP_CONCAT(emp_name ORDER BY id SEPARATOR ',') AS emp_names
FROM emp
GROUP BY gender
PostgreSQL の例
SELECT gender, string_agg(emp_name, ',' ORDER BY id) AS emp_names
FROM emp
GROUP BY gender
Oracle の例
SELECT gender, RTRIM(XMLAGG(XMLELEMENT("t", emp_name || ',') ORDER BY id).EXTRACT('//text()'), ',') AS emp_names
FROM emp
GROUP BY gender
SQL Server の例
SELECT gender, STUFF((SELECT ',' + emp_name FROM emp WHERE gender = e.gender ORDER BY id FOR XML PATH ('')), 1, 1, '') AS emp_names
FROM emp AS e
GROUP BY gender
SQL Server (2017～) の例
SELECT gender, STRING_AGG(emp_name, ',') WITHIN GROUP (ORDER BY id) AS emp_names
FROM emp
GROUP BY gender
SQLite の例
SELECT gender, GROUP_CONCAT(emp_name, ',') AS emp_names
FROM emp
GROUP BY gender

ORDER BYでNULLを最後にする (NULLS LAST)

RDBMS 例
MySQL ORDER BY 列名 IS NULL ASC, 列名
PostgreSQL ORDER BY 列名 NULLS LAST
Oracle ORDER BY 列名 NULLS LAST
SQL Server ORDER BY CASE WHEN gender IS NULL THEN 1 ELSE 0 END, 列名
SQLite (3.30～) ORDER BY 列名 NULLS LAST
  • 補足
    • NULLS LASTが使用できない製品の場合は、列名 IS NULLなどで先にソートする必要があります。
      (列名 IS NULLが動作する場合、非NULLが0(false), NULLが1(true)になるためNULLS LAST相当になります。動作しない場合、CASEを使用して同等になるようにします)
  • 参考

INSERTした自動連番IDの取得

RDBMS 例
MySQL LAST_INSERT_ID()
PostgreSQL lastval(), INSERT ... RETURNING ID列
Oracle INSERT ... RETURNING ID列
SQL Server SELECT SCOPE_IDENTITY(), OUTPUT句 (INSERT ... INTO ... OUTPUT)
SQLite last_insert_rowid()
MySQL
CREATE TABLE test1 (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  v1 INTEGER
);
INSERT INTO test1(v1) VALUES (100);

SELECT LAST_INSERT_ID();
PostgreSQL (lastval)
CREATE TABLE test1 (
  id SERIAL PRIMARY KEY,
  v1 INTEGER
);
INSERT INTO test1(v1) VALUES (100);

SELECT lastval();
PostgreSQL (RETURNING)
INSERT INTO test1(v1) VALUES (200) RETURNING id;
Oracle
CREATE TABLE test1 (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  v1 INTEGER
);

DECLARE
  -- 値を受け取るための変数の定義
  var1 number;
BEGIN
  INSERT INTO test1(v1) VALUES (100) RETURNING id INTO tmp1;
  -- 表示
  dbms_output.put_line(var1);
END;
SQL Server (SCOPE_IDENTITY)
CREATE TABLE test1 (
  id INTEGER IDENTITY(1, 1) PRIMARY KEY,
  v1 INTEGER
);
INSERT INTO test1(v1) VALUES (100);

SELECT SCOPE_IDENTITY();
SQL Server (INSERT ... INTO ... OUTPUT)
-- 値を受け取るためのテーブル変数の定義
DECLARE @var1 TABLE (id INTEGER);
-- INSERT + INSERTされた値の取得
INSERT INTO test1(v1) OUTPUT INSERTED.id INTO @var1 VALUES (200);
-- 表示
SELECT * FROM @var1;
SQLite
CREATE TABLE test1 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  v1 INTEGER
);
INSERT INTO test1(v1) VALUES (100);

SELECT last_insert_rowid();

INSERT

複数行インサート (バルクインサート)

RDBMS 例
MySQL カンマ区切り
PostgreSQL カンマ区切り
Oracle INSERT ALL
SQL Server カンマ区切り
SQLite (3.7.11～) カンマ区切り
MySQL, PostgreSQL, SQL Server, SQLite
INSERT INTO emp VALUES
  (1, 'emp1', 1),
  (2, 'emp2', 1)
Oracle
INSERT ALL
  INTO emp VALUES (1, 'emp1', 1)
  INTO emp VALUES (2, 'emp2', 1)
  SELECT * FROM dual

複数行インサート + アップデート (バルクアップサート)

RDBMS 例
MySQL ON DUPLICATE KEY UPDATE + VALUES()
PostgreSQL ON CONOFLICT ... UPDATE + EXCLUDED
MySQL
-- サンプルデータ
CREATE TABLE emp ( id BIGINT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(10), gender INTEGER );
CREATE UNIQUE INDEX emp_emp_name_uniq ON emp(emp_name);
INSERT INTO emp (emp_name, gender) VALUES
  ('emp1', 1),
  ('emp2', 1);

-- 複数行インサート + アップデート
INSERT INTO emp (emp_name, gender) VALUES
  ('emp1', 2),
  ('emp2', 2)
ON DUPLICATE KEY UPDATE emp_name = VALUES(emp_name), gender = VALUES(gender);
PostgreSQL
-- サンプルデータ
CREATE TABLE emp ( id BIGSERIAL, emp_name VARCHAR(10), gender INTEGER );
CREATE UNIQUE INDEX emp_emp_name_uniq ON emp(emp_name);
INSERT INTO emp (emp_name, gender) VALUES
  ('emp1', 1),
  ('emp2', 1);

-- 複数行インサート + アップデート
INSERT INTO emp (emp_name, gender) VALUES
  ('emp1', 2),
  ('emp2', 2)
ON CONFLICT (emp_name) DO UPDATE SET emp_name = EXCLUDED.emp_name, gender = EXCLUDED.gender;

TRUNCATE

RDBMS 例
MySQL TRUNCATE TABLE テーブル名 (TRUNCATE テーブル名 でも可)
PostgreSQL TRUNCATE TABLE テーブル名 (TRUNCATE テーブル名 でも可)
Oracle TRUNCATE TABLE テーブル名
SQL Server TRUNCATE TABLE テーブル名
SQLite DELETE FROM テーブル名;
(SQLite には TRUNCATE が無い)

+ 連番列のリセット

RDBMS 例
MySQL TRUNCATE TABLE テーブル名
PostgreSQL TRUNCATE TABLE テーブル名 RESTART IDENTITY
(RESTART IDENTITY を指定しない場合はシーケンスがリセットされない)
Oracle TRUNCATE TABLE テーブル名; ALTER TABLE テーブル名 MODIFY(列名 NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1));
SQL Server TRUNCATE TABLE テーブル名
SQLite DELETE FROM テーブル名; DELETE FROM sqlite_sequence WHERE name = 'テーブル名'

トランザクション

トランザクションの開始

RDBMS 例
MySQL BEGIN (BEGIN WORK), START TRANSACTION
PostgreSQL BEGIN (BEGIN WORK, BEGIN TRANSACTION)
Oracle なし (自動)
SQL Server BEGIN (BEGIN TRAN, BEGIN TRANSACTION)
SQLite BEGIN (BEGIN TRANSACTION)

分離レベルの設定

RDBMS 例
MySQL SET TRANSACTION ISOLATION LEVEL 分離レベル
PostgreSQL SET TRANSACTION ISOLATION LEVEL 分離レベル
Oracle SET TRANSACTION ISOLATION LEVEL 分離レベル
SQL Server SET TRANSACTION ISOLATION LEVEL 分離レベル
SQLite なし (既定で SERIALIZABLE)
指定できる分離レベル
RDBMS READ UNCOMMITED READ COMMITED REPEATABLE READ SERIALIZABLE
MySQL ○ ○ ○ ○
PostgreSQL - ○ ○ ○
Oracle - ○ - ○
SQL Server ○ ○ ○ ○

ロック

更新ロック

RDBMS 例
MySQL SELECT ... FOR UPDATE
PostgreSQL SELECT ... FOR UPDATE
Oracle SELECT ... FOR UPDATE
SQL Server SELECT ... WITH (UPDLOCK)
SQLite なし
NOWAIT
RDBMS 例
MySQL SELECT ... FOR UPDATE NOWAIT
PostgreSQL SELECT ... FOR UPDATE NOWAIT
Oracle SELECT ... FOR UPDATE NOWAIT
SQL Server SELECT ... WITH(UPDLOCK, NOWAIT)
SQLite なし

関数

NULLではない値を返す (COALESCE 系)

RDBMS 例
MySQL COALESCE(式1, 式2, ...), IFNULL(式1, 式2)
PostgreSQL COALESCE(式1, 式2, ...)
Oracle COALESCE(式1, 式2, ...), NVL(式1, 式2)
SQL Server COALESCE(式1, 式2, ...), ISNULL(式1, 式2)
SQLite COALESCE(式1, 式2, ...), IFNULL(式1, 式2)