JOIN句は複数の表を連結するための構文です。
JOINには色々と種類がありますが、良く使うのはINNER JOINです。
JOIN句の基本構文(INNER JOIN)
SELECT 表名A.列名 FROM 表名A INNER JOIN 表名B ON 表名A.列名A = 表名B.列名B
複数の表を連結するためには、それぞれにキーとなる列名が必要です。
※CROSS JOIN(直積)の場合は不要。
AlaSQLでの記述例(INNER JOIN)
下記のプログラムでは商品表に果物が5つ登録されており、特にリンゴはID:1で登録されています。また、販売表にはりんご(item_id:1)の売り上げが5つ登録されています。ばなな等の他の果物は全く売れていない状態です。これらの表をINNER JOINしてみます。
let sales = [
{id:1, item_id:1, price:200, cost:50, created:'2020-6-12 10:00:00' },
{id:2, item_id:1, price:180, cost:50, created:'2020-6-12 11:00:00' },
{id:3, item_id:1, price:250, cost:60, created:'2020-6-13 10:00:00' },
{id:4, item_id:1, price:200, cost:60, created:'2020-6-13 14:00:00' },
{id:5, item_id:1, price:150, cost:60, created:'2020-6-13 18:00:00' },
];
let items = [
{id:1, name:"りんご"},
{id:2, name:"ばなな"},
{id:3, name:"梅"},
{id:4, name:"いちご"},
{id:5, name:"すいか"}
];
let result = alasql("SELECT "
+ "items.name, "
+ "price,cost "
+ " FROM "
+ " ? AS sales "
+ " INNER JOIN ? items "
+ " on sales.item_id = items.id "
,[sales,items]);
console.log(result);
実行結果
0: {name: "りんご", price: 200, cost: 50}
1: {name: "りんご", price: 180, cost: 50}
2: {name: "りんご", price: 250, cost: 60}
3: {name: "りんご", price: 200, cost: 60}
4: {name: "りんご", price: 150, cost: 60}
解説
INNER JOINではりんごしか売れていないのでりんごの売り上げだけが取得されました。salesの表にはりんごの販売履歴が5件分記録されていたので、INNER JOINをした結果も5件となっています。
(一応)OUTER JOINの解説
OUTER JOINも一応解説します。FULL OUTER JOINやLEFT OUTER JOINや RIGHT OUTER JOINなどがありますが、とりあえずFULL OUTER JOINから。
let result = alasql("SELECT "
+ "items.name, "
+ "price,cost "
+ " FROM "
+ " ? AS sales "
+ " FULL OUTER JOIN ? items "
+ " on sales.item_id = items.id "
,[sales,items]);
console.log(result);
実行結果
0: {name: "りんご", price: 200, cost: 50}
1: {name: "りんご", price: 180, cost: 50}
2: {name: "りんご", price: 250, cost: 60}
3: {name: "りんご", price: 200, cost: 60}
4: {name: "りんご", price: 150, cost: 60}
5: {name: "ばなな", price: undefined, cost: undefined}
6: {name: "梅", price: undefined, cost: undefined}
7: {name: "いちご", price: undefined, cost: undefined}
8: {name: "すいか", price: undefined, cost: undefined}
解説
はい、売れていない商品の結果も結合されました。結果は9件になりました。売れていない果物4件分も結合されました。
LEFT OUTER JOIN
let result = alasql("SELECT "
+ "items.name, "
+ "price,cost "
+ " FROM "
+ " ? AS sales "
+ " LEFT OUTER JOIN ? items "
+ " on sales.item_id = items.id "
,[sales,items]);
console.log(result);
実行結果
0: {name: "りんご", price: 200, cost: 50}
1: {name: "りんご", price: 180, cost: 50}
2: {name: "りんご", price: 250, cost: 60}
3: {name: "りんご", price: 200, cost: 60}
4: {name: "りんご", price: 150, cost: 60}
解説
LEFT OUTER JOINの場合は、sales表に対してJOINしているため、売れていない商品の販売結果は返ってきません。
RIGHT OUTER JOIN
let result = alasql("SELECT "
+ "items.name, "
+ "price,cost "
+ " FROM "
+ " ? AS sales "
+ " RIGHT OUTER JOIN ? items "
+ " on sales.item_id = items.id "
,[sales,items]);
console.log(result);
実行結果
0: {name: "りんご", price: 200, cost: 50}
1: {name: "りんご", price: 180, cost: 50}
2: {name: "りんご", price: 250, cost: 60}
3: {name: "りんご", price: 200, cost: 60}
4: {name: "りんご", price: 150, cost: 60}
5: {name: "ばなな", price: undefined, cost: undefined}
6: {name: "梅", price: undefined, cost: undefined}
7: {name: "いちご", price: undefined, cost: undefined}
8: {name: "すいか", price: undefined, cost: undefined}
解説
RIGHT OUTER JOINの場合は、items表に対してJOINしているため、売れていない商品の販売結果も返ってきました。
CROSS JOIN
let result = alasql("SELECT "
+ "items.name, "
+ "price,cost "
+ " FROM "
+ " ? AS sales "
+ " CROSS JOIN ? items "
,[sales,items]);
console.log(result);
実行結果
0: {name: "りんご", price: 200, cost: 50}
1: {name: "ばなな", price: 200, cost: 50}
2: {name: "梅", price: 200, cost: 50}
3: {name: "いちご", price: 200, cost: 50}
4: {name: "すいか", price: 200, cost: 50}
5: {name: "りんご", price: 180, cost: 50}
6: {name: "ばなな", price: 180, cost: 50}
7: {name: "梅", price: 180, cost: 50}
8: {name: "いちご", price: 180, cost: 50}
9: {name: "すいか", price: 180, cost: 50}
10: {name: "りんご", price: 250, cost: 60}
11: {name: "ばなな", price: 250, cost: 60}
12: {name: "梅", price: 250, cost: 60}
13: {name: "いちご", price: 250, cost: 60}
14: {name: "すいか", price: 250, cost: 60}
15: {name: "りんご", price: 200, cost: 60}
16: {name: "ばなな", price: 200, cost: 60}
17: {name: "梅", price: 200, cost: 60}
18: {name: "いちご", price: 200, cost: 60}
19: {name: "すいか", price: 200, cost: 60}
20: {name: "りんご", price: 150, cost: 60}
21: {name: "ばなな", price: 150, cost: 60}
22: {name: "梅", price: 150, cost: 60}
23: {name: "いちご", price: 150, cost: 60}
24: {name: "すいか", price: 150, cost: 60}
解説
CROSS JOINの場合はsales表(5レコード) x items表(5レコード)の積として25レコードの結果が返ってきます。CROSS JOINの場合はON～は使えません（WHERE句は使えます）。