JOIN 句の例
SQL JOIN 句は、共通のフィールドに基づいて 2 つ以上のテーブルのデータを結合するために使用されます。結果は、指定した結合方法によって変わる場合もあります。JOIN 句の詳細については、「パラメータ」を参照してください。
次の例では、TICKITサンプルデータのデータを使用します。データベーススキーマの詳細については、「サンプルデータベース」を参照してください。サンプルデータをロードする方法については、「Amazon Redshift 入門ガイド」の「データのロード」を参照してください。
次のクエリは、LISTING テーブルと SALES テーブル間の内部結合です (JOIN キーワードを除く)。ここで、LISTING テーブルの LISTID は 1～5 です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing, sales
where listing.listid = sales.listid
and listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
次のクエリは左外部結合です。左と右の外部結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したどちらかのテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 2 および 3 にセールスがないことを示しています。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing left outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
次のクエリは右外部結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 1、4、および 5 が基準に一致することを示しています。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing right outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
次のクエリは完全結合です。完全結合は、もう一方のテーブルに一致するものが見つからない場合に、結合したテーブルの値を保持します。左のテーブルと右のテーブルは、構文に一覧表示されている最初と 2 番目のテーブルです。NULL 値は、結果セットの「ギャップ」を埋めるために使われます。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。結果は、LISTID 2 および 3 にセールスがないことを示しています。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
次のクエリは完全結合です。このクエリは、LISTING テーブル (左のテーブル) と SALES テーブル (右のテーブル) の LISTID 列の値と一致します。セールスがない行 (LISTID 2 および 3) のみが結果に表示されます。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
and (listing.listid IS NULL or sales.listid IS NULL)
group by 1
order by 1;
listid | price | comm
-------+--------+--------
2 | NULL | NULL
3 | NULL | NULL
次の例は、ON 句を含む内部結合です。この場合、NULL 行は返されません。
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
次のクエリは、LISTING テーブルと SALES テーブルのクロス結合またはデカルト結合で、結果を制限する述語が使用されています。このクエリは、両方のテーブルの LISTID 1、2、3、4、および 5 について、SALES テーブルと LISTING テーブルの LISTID 列の値と一致します。結果は、20 行が基準に一致することを示しています。
select sales.listid as sales_listid, listing.listid as listing_listid
from sales cross join listing
where sales.listid between 1 and 5
and listing.listid between 1 and 5
order by 1,2;
sales_listid | listing_listid
-------------+---------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
5 | 1
5 | 1
5 | 2
5 | 2
5 | 3
5 | 3
5 | 4
5 | 4
5 | 5
5 | 5
次の例は、2 つのテーブル間の自然結合です。この場合、listid、sellerid、eventid、および dateid の各列は、両方のテーブルで同じ名前とデータ型を持つため、結合列として使用されます。結果は 5 行に制限されます。
select listid, sellerid, eventid, dateid, numtickets
from listing natural join sales
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+-----------+---------+--------+-----------
113 | 29704 | 4699 | 2075 | 22
115 | 39115 | 3513 | 2062 | 14
116 | 43314 | 8675 | 1910 | 28
118 | 6079 | 1611 | 1862 | 9
163 | 24880 | 8253 | 1888 | 14
次の例は、USING 句が含まれている 2 つのテーブル間の結合です。この場合、listid と eventid の各列が結合列として使用されます。結果は 5 行に制限されます。
select listid, listing.sellerid, eventid, listing.dateid, numtickets
from listing join sales
using (listid, eventid)
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+----------+---------+--------+-----------
1 | 36861 | 7872 | 1850 | 10
4 | 8117 | 4337 | 1970 | 8
5 | 1616 | 8647 | 1963 | 4
5 | 1616 | 8647 | 1963 | 4
6 | 47402 | 8240 | 2053 | 18
次のクエリは、FROM 句の 2 つのサブクエリを内部結合したものです。このクエリは、イベント (コンサートとショー) の異なるカテゴリのチケットの販売数と売れ残り数を検出します。この FROM 句サブクエリはテーブルサブクエリです。これらは、複数の列と行を返すことができます。
select catgroup1, sold, unsold
from
(select catgroup, sum(qtysold) as sold
from category c, event e, sales s
where c.catid = e.catid and e.eventid = s.eventid
group by catgroup) as a(catgroup1, sold)
join
(select catgroup, sum(numtickets)-sum(qtysold) as unsold
from category c, event e, sales s, listing l
where c.catid = e.catid and e.eventid = s.eventid
and s.listid = l.listid
group by catgroup) as b(catgroup2, unsold)
on a.catgroup1 = b.catgroup2
order by 1;
catgroup1 | sold | unsold
----------+--------+--------
Concerts | 195444 |1067199
Shows | 149905 | 817736
