SQLite3を用いたSQL入門（その2）

はじめに_

以下を実行していることを前提とする。

本ページでのLinux/Unixコマンドの表記方法_

本ページではターミナル上で入力する命令（Linux/Unixコマンド、あるいはコマンドという）をパーセント記号（％）の後ろに表記する。たとえば以下のように表記する。

% Linux/Unixコマンド

Linux/Unixコマンドの一つであるlsを表記する場合は以下のようにあらわす。これはターミナル上でlsという文字列を入力し、そのごEnterキーを押すという意味である。

% ls

また、必要に応じてLinux/Unixコマンドを実行した際の出力結果も例として示す。出力結果は冒頭にパーセント記号がついていない文字列である。以下の表記例は ls -1 というコマンドを実行した結果としてgotohという文字列が表示されたことを表す。

% ls -1
gotoh

これとは別に、SQLite3中で入力するコマンドは「>」に並べて以下のように表現している。

> SELECT * FROM goods;

トラブルシューティング_

SQLite3を用いたSQL入門（続）_

演習準備_

SQLite3を用いたSQL入門（その1）「練習：UPDATE 復習」が終了したデータベースを以下の手順で用意する。

必要に応じてwgetをインストールする。

% which wget （何も表示されていなければインストールが必要）
  • Ubuntuの場合 
    % sudo apt install wget
% which wget
  • Mac OSの場合 
    % brew install wget
% which wget

データベースquiz2.dbをダウンロードし、以後の演習で使用するデータベース名test.dbとしてコピーする。

% cd
% mkdir -p ~/IntroSQL
% cd IntroSQL
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/quiz2.db quiz2.db
% cp quiz2.db test.db

データベースのバックアップ（その１：ファイルコピー、非SQL）_

このあとにDELETEコマンドを練習するため、その前に現時点でのデータベースのバックアップをとる練習を行う。

SQLite3ではデータベースを１つのファイルに格納している。test.dbというファイルがあるか確認する。

% ls

SQLite3の場合、このデータベースが格納されているファイルをコピーしておけばバックアップを簡単にとることができる。Linuxコマンドのcpコマンドを使う。

% cp -p test.db test.db.YYYYMMDD (YYYYは西暦4桁表記、MMは月の2桁表記、DDは日の2桁表記）

データベースのバックアップ（その２：SQLファイルの書き出し、非SQL）_

別の方法でもデータベースのバックアップを取ることができる。こちらの方法の方が他のデータベース管理システムにもデータの引き継ぎができるため一般的な方法である。

SQLite3でデータベースを読み込む。

% sqlite3 test.db

データベースを再現するためのSQL文をファイルに書き出す。このようなデータベース内のデータを外部ファイルに書き出す機能をダンプ（dump）という。多くのRDBMSではダンプ機能（コマンド）を用意している。

sqlite> .output test_db_YYYYMMDD.sql
sqlite> .dump
sqlite> .quit

ファイルを確認してみる。catコマンドはファイルの中身をターミナル上に表示するコマンドである。

% ls

% cat test_db_YYYYMMDD.sql （YYYYMMDDは自分が入力した値に置き換えること）
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int);
INSERT INTO goods VALUES('A01','Office Paper A4',2000);
～省略～
INSERT INTO orders VALUES(1,1301,'A01',5);
INSERT INTO orders VALUES(2,1301,'A02',2);
INSERT INTO orders VALUES(1,4001,'B01',1);
COMMIT;

データの削除（DELETE）_

構文は以下の通り。

DELETE
FROM
 table
WHERE
 search_condition;

補足

  • WHERE句で更新する行を限定しないと、テーブル内のすべての行が削除される。
  • WHERE句の書き方はSELECT文と全く同じなので、まず、SELECT文で自分の更新したい行だけWHERE句で抜き出すことができるのを確認した上でDELETE文を実行する方がよい。

以下の入力例を試してみる。

% sqlite3 test.db
sqlite> SELECT * FROM goods where gid = 'C01';
C01|White Board|14000

sqlite> DELETE FROM goods where gid = 'C01';

sqlite> SELECT * FROM goods where gid = 'C01';
（削除されているので何も表示されない。）

手がすべってみる。

sqlite> SELECT * FROM goods;
C01|White Board|14000
sqlite> DELETE FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods;
A01|Office Paper A4|2000
A03|Office Paper B5|1500
A02|Office Paper A3|4000
B01|Toner Cartridge Black|25000

sqlite> DELETE FROM goods;

sqlite> SELECT * FROM goods;
（削除されているので何も表示されない。）

練習：DELETEの復習_

WHERE句において、あるフィールドの値に特定の文字列を含む行だけを抜き出す場合には「フィールド名 like "%特定の文字列%"」という条件式を用いる。

たとえば、以下のテーブルcustomersにおいてaddressにcityを含む行を抜き出す場合は以下のように入力する。

テーブル: customers

cidcnameaddress
101A corp.Chuoh-ku, Sapporo city, Hokkaido
201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka
sqlite> SELECT * FROM customers;
101|A corp.|Chuoh-ku, Sapporo city, Hokkaido
201|C corp.|Hirosaki city, Aomori
1301|A corp.|Chiyoda-ku, Tokyo
1302|B corp.|Mitaka city, Tokyo
2301|D corp.|Chigusa-ku, Nagoya city, Aichi
4001|E corp.|Iizuka city, Fukuoka

sqlite> SELECT * FROM customers WHERE address like "%city%";
101|A corp.|Chuoh-ku, Sapporo city, Hokkaido
201|C corp.|Hirosaki city, Aomori
1302|B corp.|Mitaka city, Tokyo
2301|D corp.|Chigusa-ku, Nagoya city, Aichi
4001|E corp.|Iizuka city, Fukuoka

以上を踏まえて、DELETE文を用いて、customersにおいて「～区」を含む行を削除せよ。

データベースのリストア（その１：ファイルのコピー、非SQL）_

バックアップしておいたデータを使用できる状態に戻すことをリストア（復元、restore）という。ファイルのコピーでバックアップしておいたデータは以下のようにリストアする。

先ほどのファイルのコピーでバックしたデータを使って復元する。コピーしたファイル名がtest.db.YYYYMMDDのとき、以下のように入力する。（なお、SQLite3を一度終了させること。その後、実行する）

% cp test.db.YYYYMMDD test.db

復旧していることを確認する。先ほど内容を削除した goodsテーブルの中身が復旧していることを確認する。

% sqlite3 test.db
sqlite> SELECT * FROM goods;
C01|White Board|14000
sqlite> DELETE FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods;
A01|Office Paper A4|2000
A03|Office Paper B5|1500
A02|Office Paper A3|4000
B01|Toner Cartridge Black|25000

次の作業のため、一旦終了する。

sqlite>  .quit

データベースのリストア（その２：SQL文の挿入、非SQL）_

SQL文でダンプしたデータを使ってリストアする。ダンプしたSQL文がtest_db_YYYYMMDD.sqlであるとき、以下のように入力する。

% sqlite3 new.db < test_db_YYYYMMDD.sql

外部に出力したSQL文からデータベースが復旧されていることを確認する。

% sqlite3 new.db

sqlite> .schema
CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int);
CREATE TABLE customers ( cid int PRIMARY KEY, cname char(20), address char(40) );
CREATE TABLE orders( oid int NOT NULL, cid int NOT NULL, gid char(3) NOT NULL, --改行する
number int, PRIMARY KEY (oid, cid), FOREIGN KEY (cid) REFERENCES customers (cid),  --改行する
FOREIGN KEY (gid) REFERENCES goods (gid), CHECK (number > 0));

sqlite> SELECT * FROM goods;
C01|White Board|14000
sqlite> DELETE FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods where gid = 'C01';
sqlite> SELECT * FROM goods;
A01|Office Paper A4|2000
A03|Office Paper B5|1500
A02|Office Paper A3|4000
B01|Toner Cartridge Black|25000

sqlite> SELECT * FROM customers;
～出力省略～

sqlite> SELECT * FROM orders;
～出力省略～

データベースの中身をSQL文として出力し、バックアップをとっておくのは、他のRDBMS（たとえば、PostgreSQLなど）へ移行するときにも役に立つ。

ビュー (CREATE VIEW)_

SELECT文によりある条件をみたしたデータを参照できる。しかし、複雑なSELECT文を頻繁に使用する場合、毎回、SQL文を作成するのは大変である。

そこで、SELECT文により生成されるテーブルを仮想的なテーブルとして保存する機能がビューである。データベースの構成（テーブルやフィールド名など）を変えたとしても、ある用途やアプリケーションから参照されるテーブルをビューを用いて容易に再現することができる。

構文は以下の通り。

CREATE [TEMP] VIEW [IF NOT EXISTS] view_name(column-name-list)
AS 
   select-statement;

SQL文からリストアしたデータベースを利用して、以下の演習を実行する。

sqlite3 new.db

まず、現在のテーブル構成を確認する。3つのテーブルが存在する。

sqlite> .tables
customers    goods     orders

以下の例ではgidとgnameからなるビュー goods_listsを作成している。作成したビューは実テーブル（CREATE TABLEで作ったテーブル）と同様にSELECT文で参照できる。

sqlite> CREATE VIEW goods_lists (gid, gname) AS SELECT gid,gname FROM goods;

sqlite> .tables
customers    goods        goods_lists  orders 

sqlite> SELECT * from goods_lists;
A01|Office Paper A4
A03|Office Paper B5
A02|Office Paper A3
B01|Toner Cartridge Black
C01|White Board

別のビューを作成してみる。2000円以下の品物だけを登録しているビューを作成する。

sqlite> CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000;

sqlite> .tables
customers    goods        goods_lists  low_prices   orders   

sqlite> SELECT * FROM low_prices;
A01|Office Paper A4|2000
A03|Office Paper B5|1500

練習：CREATE VIEWの復習_

テーブルcutomersから東京にある会社だけを抜き出したビュー tokyo_corpを作成せよ。

テーブル: customers

cidcnameaddress
0101A corp.Chuoh-ku, Sapporo city, Hokkaido
0201C corp.Hirosaki city, Aomori
1301A corp.Chiyoda-ku, Tokyo
1302B corp.Mitaka city, Tokyo
2301D corp.Chigusa-ku, Nagoya city, Aichi
4001E corp.Iizuka city, Fukuoka

ビューの削除（DROP VIEW）_

構文は以下の通り。

DROP VIEW [IF EXISTS] view_name;

定義済みのVIEWを確認する（非SQL）。

sqlite> .tables
customers    goods        goods_lists  low_prices   orders       tokyo_corp 

sqlite> .schema
CREATE TABLE goods ( gid char(3) PRIMARY KEY, gname char(30), price int);
CREATE TABLE customers ( cid int PRIMARY KEY, cname char(20), address char(40) );
CREATE TABLE orders( oid int NOT NULL, cid int NOT NULL, gid char(3) NOT NULL, --改行する
number int, PRIMARY KEY (oid, cid), FOREIGN KEY (cid) REFERENCES customers (cid),  --改行する
FOREIGN KEY (gid) REFERENCES goods (gid), CHECK (number > 0));
CREATE VIEW goods_lists (gid, gname) AS SELECT gid,gname FROM goods
/* goods_lists(gid,gname) */;
CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000
/* low_prices(gid,gname,price) */;
CREATE VIEW tokyo_corp as select * from customers where address like "%Tokyo%"
/* tokyo_corp(cid,cname,address) */;

ビュー goods_listsを削除する。

sqlite> DROP VIEW goods_lists;

sqlite> .tables
customers   goods       low_prices  orders      tokyo_corp

テーブルの削除（DROP TABLE）_

構文は以下の通り。

DROP TABLE [IF EXISTS] [schema_name.]table_name;

たとえば、goodsテーブルを削除してみる。

sqlite> .tables
customers   goods       low_prices  orders      tokyo_corp

sqlite> DROP TABLE goods;

sqlite> .tables
customers   low_prices  orders      tokyo_corp

ビューlow_pricesの本体はテーブルgoodsの内容を閲覧するSELECT文であるため、goodsを削除したあとにlow_pricesを閲覧するとエラーが表示される。

sqlite> .schema low_prices
CREATE VIEW low_prices AS SELECT * FROM goods WHERE price <= 2000;

sqlite> .tables
customers   low_prices  orders      tokyo_corp

sqlite> SELECT * from low_prices;
Error: no such table: main.goods

テーブルの定義の変更（ALTER TABLE）_

テーブルの定義を変更する場合にはALTER TABLE文を用いる。テーブル名、フィールド名、各フィールドの値など変更する場所ごとに記述法が異なるので、以下のページを参照のこと。

SQLite3を用いたSQLの高度な問合せ_

以下のチュートリアルで使うデータベース_

データベースをダウンロードする。

% cd
% mkdir -p IntroSQL
% cd IntroSQL

% which wget
/usr/bin/wget

% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/edu.db
% wget http://www.aise.ics.saitama-u.ac.jp/~gotoh/Lectures/TUS_IP/download/create_table.sql

% ls
create_table.sql  edu.db

表：students（学生）

sid（学生ID）sname（氏名）highschool（出身高校）
501Keitha TeagueUrawa High School
601Marybeth BorosWarabi High School
602Shasta PepperSaitama Sakae High School
701Peg MassingillShukutoku Yono High School
702Eugenio DenglerJohoku High School

表：credits（修得単位）

sid（学生ID）degree （学年）unit（学年ごとの修得単位数）
501148
501240
501338
601139
701137
702145

表：classes （履修）

sid（学生ID）cname（科目名）unit（単位数）
501Graduation Research8
601Web Design2
601Image Processing4
601Computer Graphics2
603Database6
603Web Design2
701Information Retrieval2
701Programming Lang Java4
702Programming Lang Java4
702Web Design2

テーブルの名前と定義を確認する。

% sqlite3 edu.db
sqlite> .tables
classes   credits   students

sqlite> .schema
CREATE TABLE students(
       sid char(4),
       sname char(30),
       highschool char(30),
       PRIMARY KEY(sid)
       );
CREATE TABLE credits(
       sid char(4),
       degree int,
       unit int,
       PRIMARY KEY(sid,degree)
       );
CREATE TABLE classes(
       sid char(4),
       cname char(30),
       unit int,
       PRIMARY KEY(sid,cname)
       );

テーブル内の値を確認する。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM credits;
501|1|48
501|2|40
501|3|38
601|1|39
701|1|37
702|1|45

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

distict句とorder by句_

構文は以下の通り。

SELECT DISTINCT column_list
FROM table_list
  JOIN table ON join_condition
WHERE row_filter
ORDER BY column
LIMIT count OFFSET offset
GROUP BY column
HAVING group_filter;

SELECT文にDISTINCT句をつけると問合せ結果から重複を取り除くことができる。以下の問合せを行ってみる。

sqlite> SELECT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Web Design
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design

sqlite> SELECT DISTINCT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Information Retrieval
Programming Lang Java

ORDER BY句を用いるとSELECTの問合せ結果を指定した列（フィールド）について辞書順にならべることができる。辞書順には昇順（ASC）と降順（DESC）が存在し、標準は昇順になっている。以下の問合せを行ってみる。

sqlite> SELECT cname FROM classes;
Graduation Research
Computer Graphics
Image Processing
Web Design
Database
Web Design
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design

sqlite> SELECT cname FROM classes ORDER BY cname;
Computer Graphics
Database
Graduation Research
Image Processing
Information Retrieval
Programming Lang Java
Programming Lang Java
Web Design
Web Design
Web Design

sqlite> SELECT cname FROM classes ORDER BY cname DESC;
Web Design
Web Design
Web Design
Programming Lang Java
Programming Lang Java
Information Retrieval
Image Processing
Graduation Research
Database
Computer Graphics

DISTINCT句とORDER BY句を同時につかうこともできる。重複している科目が１つにまとめられていることに注目。

sqlite> SELECT DISTINCT cname FROM classes ORDER BY cname;
Computer Graphics
Database
Graduation Research
Image Processing
Information Retrieval
Programming Lang Java
Web Design

sqlite> SELECT DISTINCT cname FROM classes ORDER BY cname DESC;
Web Design
Programming Lang Java
Information Retrieval
Image Processing
Graduation Research
Database
Computer Graphics

和集合演算（union）、共通集合演算（intersect）、差集合演算（except）、直積_

構文は以下のとおり。

query_1
UNION [ALL]
query_2
UNION [ALL]
query_3
...;

UNIONを使うと問合せ（query）結果の和集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
Information Retrieval
Programming Lang Java

sqlite> SELECT cname FROM classes WHERE sid = '702';
Programming Lang Java
Web Design

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> UNION
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval
Programming Lang Java
Web Design

INTERSECT句を使うと問合せ（query）結果の共通集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '702';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> INTERSECT
   ...> SELECT cname FROM classes WHERE sid = '702';
Programming Lang Java

EXCEPT句を使うと問合せ（query）結果の差集合演算を行うことができる。

sqlite> SELECT cname FROM classes WHERE sid = '701';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '702';
～出力省略～

sqlite> SELECT cname FROM classes WHERE sid = '701'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '702';
Information Retrieval

sqlite> SELECT cname FROM classes WHERE sid = '702'
   ...> EXCEPT
   ...> SELECT cname FROM classes WHERE sid = '701';
Web Design

直積演算はSELECT文で実行できる。

sqlite> SELECT * FROM students;
501|Keitha Teague|Urawa High School
601|Marybeth Boros|Warabi High School
602|Shasta Pepper|Saitama Sakae High School
701|Peg Massingill|Shukutoku Yono High School
702|Eugenio Dengler|Johoku High School

sqlite> SELECT * FROM classes;
501|Graduation Research|8
601|Web Design|2
601|Image Processing|4
601|Computer Graphics|2
603|Database|6
603|Web Design|2
701|Information Retrieval|2
701|Programming Lang Java|4
702|Programming Lang Java|4
702|Web Design|2

sqlite> SELECT * FROM students, classes;
501|Keitha Teague|Urawa High School|501|Graduation Research|8
501|Keitha Teague|Urawa High School|601|Web Design|2
501|Keitha Teague|Urawa High School|601|Image Processing|4
501|Keitha Teague|Urawa High School|601|Computer Graphics|2
501|Keitha Teague|Urawa High School|603|Database|6
～省略～
702|Eugenio Dengler|Johoku High School|701|Information Retrieval|2
702|Eugenio Dengler|Johoku High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

CROSS JOIN句を用いても直積演算を実行できる。

以下の4つの文は同じ結果となる。

sqlite> SELECT * FROM students, classes;
～出力省略～

sqlite> SELECT * FROM students JOIN classes;
～出力省略～

sqlite> SELECT * FROM students INNER JOIN classes;
～出力省略～

sqlite> SELECT * FROM students CROSS JOIN classes;
～出力省略～

内部結合（JOIN / INNER JOIN）_

結合対象となるテーブルから結合条件を満たす行だけを取り出す演算を内部結合という。結合条件を満たさない行は欠落する。

SQL-89とSQL-92でそれぞれ規格化された構文があり、ほとんどのDBMSで双方に対応している。

sqlite> SELECT * FROM students AS S, credits AS C WHERE S.sid = C.sid; -- (1)
501|Keitha Teague|Urawa High School|501|1|48
501|Keitha Teague|Urawa High School|501|2|40
501|Keitha Teague|Urawa High School|501|3|38
601|Marybeth Boros|Warabi High School|601|1|39
701|Peg Massingill|Shukutoku Yono High School|701|1|37
702|Eugenio Dengler|Johoku High School|702|1|45

sqlite> SELECT * FROM students AS S JOIN credits AS C ON S.sid = C.sid; -- (2)
501|Keitha Teague|Urawa High School|501|1|48
501|Keitha Teague|Urawa High School|501|2|40
501|Keitha Teague|Urawa High School|501|3|38
601|Marybeth Boros|Warabi High School|601|1|39
701|Peg Massingill|Shukutoku Yono High School|701|1|37
702|Eugenio Dengler|Johoku High School|702|1|45

sqlite> SELECT * FROM students JOIN credits USING (sid); -- (3)
501|Keitha Teague|Urawa High School|1|48
501|Keitha Teague|Urawa High School|2|40
501|Keitha Teague|Urawa High School|3|38
601|Marybeth Boros|Warabi High School|1|39
701|Peg Massingill|Shukutoku Yono High School|1|37
702|Eugenio Dengler|Johoku High School|1|45

(1)はSQL-89で規格化された書き方。FROM句に結合対象のテーブルをすべて列挙し、WHERE句で結合条件を指定する。

(2)to(3)はSQL-92で規格化された書き方。「テーブル名 JOIN テーブル名 ON 結合条件」あるいは「テーブル名 JOIN テーブル名 USING (結合する属性)」という構文で2つのテーブルを結合する。

なお、文中の「テーブル名 AS 略記号」は結合条件を記載する際にテーブル名を略記するためのもの。

(1)と(2)はsidの列が複数個表示される。(3)はsidの列が一つだけ表示される。すなわち、(3)は自然結合演算の結果となっている。

3つ以上のテーブルも結合することができる。

sqlite> SELECT * FROM students AS S, credits AS C, classes AS R
   ...> WHERE S.sid = C.sid AND C.sid = R.sid; -- (4)；
501|Keitha Teague|Urawa High School|501|1|48|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|2|40|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|3|38|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|1|39|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|1|39|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|1|39|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Web Design|2

sqlite> SELECT * FROM students AS S JOIN credits AS C ON S.sid = C.sid
   ...> JOIN classes AS R ON C.sid = R.sid; --(5)
501|Keitha Teague|Urawa High School|501|1|48|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|2|40|501|Graduation Research|8
501|Keitha Teague|Urawa High School|501|3|38|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|1|39|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|1|39|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|1|39|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|1|37|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|1|45|702|Web Design|2

sqlite> SELECT * FROM students JOIN credits USING (sid)
   ...> JOIN classes USING (sid); -- (6)
501|Keitha Teague|Urawa High School|1|48|Graduation Research|8
501|Keitha Teague|Urawa High School|2|40|Graduation Research|8
501|Keitha Teague|Urawa High School|3|38|Graduation Research|8
601|Marybeth Boros|Warabi High School|1|39|Web Design|2
601|Marybeth Boros|Warabi High School|1|39|Image Processing|4
601|Marybeth Boros|Warabi High School|1|39|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|1|37|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|1|37|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|1|45|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|1|45|Web Design|2

なお、「JOIN」と記載している部分は「INNER JOIN」と記載してもよい。

sqlite> SELECT * FROM students AS S INNER JOIN credits AS C ON S.sid = C.sid; -- (7)
～出力省略～

sqlite> SELECT * FROM students INNER JOIN credits USING (sid); -- (8)
～出力省略～

外部結合（OUTER JOIN）_

内部結合により結合条件を満たす行が取り出されるが、外部結合では結合条件を満たさない行も取り出される。結合条件を満たしていない行については空値としてNULL値が挿入される。このため、外部結合を行う場合には該当する列にNOT NULL条件を付与してはいけない。

外部結合には左外部結合（LEFT OUTER JOIN）、右外部結合（RIGHT OUTER JOIN）、完全外部結合（FULL OUTER JOIN）の3種類がある。

内部結合に対応させる形で外部結合の働きを確かめてみる。

sqlite>  SELECT * FROM students AS S INNER JOIN classes AS R ON S.sid = R.sid;
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Web Design|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite>  SELECT * FROM students AS S LEFT OUTER JOIN classes AS R ON S.sid = R.sid; -- (9) 左外部結合。classesの行に空値が挿入される。
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
602|Shasta Pepper|Saitama Sakae High School|||
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2

sqlite>  SELECT * FROM students AS S RIGHT OUTER JOIN classes AS R ON S.sid = R.sid; -- (10) 右外部結合。studentsの行に空値が挿入される。
（Ubuntu 22.04LTS, Monterey, バージョン 3.37の場合）
Error: in prepare, RIGHT and FULL OUTER JOINs are not currently supported (1)

(Ventura, バージョン3.39以降の場合）
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2
|||603|Database|6
|||603|Web Design|2

sqlite>  SELECT * FROM students AS S FULL OUTER JOIN classes AS R ON S.sid = R.sid; -- (11) 完全外部結合。studentsの行に空値が挿入される。

（Ubuntu 22.04LTS, Monterey, バージョン 3.37の場合）
Error: in prepare, RIGHT and FULL OUTER JOINs are not currently supported (1)

(Ventura, バージョン3.39以降の場合）
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
602|Shasta Pepper|Saitama Sakae High School|||
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2
|||603|Database|6
|||603|Web Design|2

(10)と(11)のSQL文はSQLite3 バージョン3.39.0以前では実行できない。 バージョン3.39.0以降では右外部結合と完全外部結合を扱うことができる。

sqlite3 バージョン3.39.0以降では右外部結合と完全外部結合を扱うことができるようになった。

ただし、2023年10月6日時点のmacOS Montereyの標準のsqlite3のバージョンは3.37のため右外部結合と完全外部結合は使えない。brewでインストールしたsqlite3は3.39.0以降なので扱うことができる。

重複したsidを取り除く場合はUSING句を用いる。

sqlite>  SELECT * FROM students INNER JOIN classes USING (sid); -- (12)
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Web Design|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Computer Graphics|2
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2

sqlite>  SELECT * FROM students LEFT OUTER JOIN classes USING (sid); -- (13) 左外部結合。classesの行に空値が挿入される。
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Computer Graphics|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Web Design|2
602|Shasta Pepper|Saitama Sakae High School||
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2

余談：Montereyで SQLite3 バージョン3.39以降を使う_

2023年10月6日時点のmacOS Montereyの標準のsqlite3のバージョンは以下の通り。

% which sqlite3
/usr/bin/sqlite3

% sqlite3 --version
3.37.0 2021-12-09 01:34:53 9ff244ce0739f8ee52a3e9671adb4ee54c83c640b02e3f9d185fd2f9a179aapl

brewでインストールした場合は、sqlite3は /usr/local/opt/sqlite3/bin/sqlite3 あるいは /opt/homebrew/opt/sqlite3/bin/sqlite3 にインストールされている。こちらはバージョン3.39.0以降なので右外部結合と完全外部結合を扱える。

% brew install sqlite3

% /usr/local/opt/sqlite3/bin/sqlite3 --version
3.43.1 2023-09-11 12:01:27 2d3a40c05c49e1a49264912b1a05bc2143ac0e7c3df588276ce80a4cbc9bd1b0 (64-bit)

% /usr/local/opt/sqlite3/bin/sqlite3 edu.db 
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
sqlite> SELECT * FROM students AS S RIGHT OUTER JOIN classes AS R ON S.sid = R.sid; -- (10) 右外部結合。studentsの行に空値が挿入される。
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2
|||603|Database|6
|||603|Web Design|2

sqlite> SELECT * FROM students AS S RIGHT OUTER JOIN classes using(sid);
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Computer Graphics|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Web Design|2
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2
603|||Database|6
603|||Web Design|2

sqlite> SELECT * FROM students AS S FULL OUTER JOIN classes AS R ON S.sid = R.sid; -- (11) 完全外部結合。studentsの行に空値が挿入される。
501|Keitha Teague|Urawa High School|501|Graduation Research|8
601|Marybeth Boros|Warabi High School|601|Computer Graphics|2
601|Marybeth Boros|Warabi High School|601|Image Processing|4
601|Marybeth Boros|Warabi High School|601|Web Design|2
602|Shasta Pepper|Saitama Sakae High School|||
701|Peg Massingill|Shukutoku Yono High School|701|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|701|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|702|Web Design|2
|||603|Database|6
|||603|Web Design|2

sqlite> SELECT * FROM students AS S FULL OUTER JOIN classes USING(sid);
501|Keitha Teague|Urawa High School|Graduation Research|8
601|Marybeth Boros|Warabi High School|Computer Graphics|2
601|Marybeth Boros|Warabi High School|Image Processing|4
601|Marybeth Boros|Warabi High School|Web Design|2
602|Shasta Pepper|Saitama Sakae High School||
701|Peg Massingill|Shukutoku Yono High School|Information Retrieval|2
701|Peg Massingill|Shukutoku Yono High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Programming Lang Java|4
702|Eugenio Dengler|Johoku High School|Web Design|2
603|||Database|6
603|||Web Design|2

練習：集合演算と結合演算_

表 studentsと表 classesを左外部結合する文(13)は以下のとおりである。

sqlite>  SELECT * FROM students LEFT OUTER JOIN classes USING (sid); -- (13) 左外部結合。classesの行に空値が挿入される。

SQLite3は右外部結合が実装されていないが、右外部結合の結果および完全外部結合の結果をSQL文を用いて得ることができる。表 studentsと表 classesの右外部結合および完全外部結合と同等の結果を得るSQL文を書け。

ヒント

  • SELECT文では問合せ結果の列（フィールド、属性）の順番を任意に指定することができる。
  • 完全外部結合は左外部結合と右外部結合の和集合演算である。
  • 解答例

リンク_

戻る_