いつも忘れるのでsqlite3でJOINの違いメモ

gistfile1.txt

#
# いつも忘れるのでsqlite3でJOINの違いメモ
#
#  詳しい違いはこの辺参照のこと
#  
#  Join (SQL) - Wikipedia, the free encyclopedia
#  http://en.wikipedia.org/wiki/Join_(SQL)

$ rm -rf a.db
$ sqlite3 a.db

sqlite> -- 以下、表示設定
sqlite> .header on
sqlite> .mode column
sqlite> .nullvalue null

sqlite> -- テーブルを作る
sqlite> create table customers(id int primary key, name varchar(255));
sqlite> create table orders(id int primary key, customer_id int not null, name varchar(255));

sqlite> -- データ入れる
sqlite> insert into customers(id, name) values(1, "テーブル1");
sqlite> insert into customers(id, name) values(2, "テーブル2");
sqlite> insert into customers(id, name) values(3, "テーブル3");
sqlite> insert into customers(id, name) values(5, "テーブル5");
sqlite> insert into orders(id, customer_id, name) values(1, 1, "ビール(中)");
sqlite> insert into orders(id, customer_id, name) values(2, 2, "ビール(中)");
sqlite> insert into orders(id, customer_id, name) values(3, 3, "ビール(中)");
sqlite> insert into orders(id, customer_id, name) values(4, 4, "ビール(中)");
sqlite> insert into orders(id, customer_id, name) values(5, 1, "もつにこみ");
sqlite> insert into orders(id, customer_id, name) values(6, 2, "とりから");
sqlite> insert into orders(id, customer_id, name) values(7, 3, "もろきゅ");

sqlite> -- お客さまテーブルの内容
sqlite> select * from customers;
id          name
----------  -------------
1           テーブル1
2           テーブル2
3           テーブル3
5           テーブル5


sqlite> -- 注文テーブルの内容
sqlite> select * from orders;
id          customer_id  name
----------  -----------  --------------
1           1            ビール(中)
2           2            ビール(中)
3           3            ビール(中)
4           4            ビール(中)
5           1            もつにこみ
6           2            とりから
7           3            もろきゅ


sqlite> -- inner join
sqlite> -- (customer_id=4の注文は表示されない)
sqlite> select orders.id, orders.name, customer_id, customers.name 
           from orders join customers on orders.customer_id = customers.id;

id          name            customer_id  name
----------  --------------  -----------  -------------
1           ビール(中)      1            テーブル1
2           ビール(中)      2            テーブル2
3           ビール(中)      3            テーブル3
5           もつにこみ      1            テーブル1
6           とりから        2            テーブル2
7           もろきゅ        3            テーブル3

sqlite> -- left outer join
sqlite> -- (左テーブルはすべて表示される。customer_id=4の注文はcustomer.name=nullで表示される)
sqlite> select orders.id, orders.name, customer_id, customers.name 
           from orders left join customers on orders.customer_id = customers.id;

id          name            customer_id  name
----------  --------------  -----------  -------------
1           ビール(中)      1            テーブル1
2           ビール(中)      2            テーブル2
3           ビール(中)      3            テーブル3
4           ビール(中)      4            null
5           もつにこみ      1            テーブル1
6           とりから        2            テーブル2
7           もろきゅ        3            テーブル3