頭脳一式

人の記憶なんて曖昧なもの。すべての情報を頭に記憶するなんてナンセンス。困ったらここに来ればいいじゃん?というスタンスで最強のナレッジベースを目指すブログ

【SQL】副問い合せ(サブクエリ)の書き方4選

副問い合せ(サブクエリ)とは

SQL文の中にSELECT文をネストする記法のこと。
内側のSELECT文(ネストしたSELECT文)の実行結果に基づいて、外側のSQL文を実行することができる。

次の2つのテーブルを使って副問い合せ(サブクエリ)の書き方をまとめてみる。

<表名:株(kabu)>

ティッカー
ticker
銘柄名
stockName
時価総額(千ドル)
cap
市場
market
セクター
sector
AAPL アップル 942231955 NASDAQ テクノロジー
AMZN アマゾン 817117254 NASDAQ 生活必需品
MSFT マイクロソフト 780843362 NASDAQ テクノロジー
BABA アリババ 526175666 NASDAQ 生活必需品
FB フェイスブック 453576433 NASDAQ コミュニケーション
GOOG グーグル 391130080 NASDAQ コミュニケーション
JPM JPモルガン 378304764 NYSE ファイナンシャル
XOM エクソンモービル 353948559 NYSE エネルギー

<表名:ポートフォリオ(portfolio)>

ティッカー
ticker
銘柄名
stockName
枚数
AMZN アマゾン 5
NFLX ネットフリックス 8
XOM エクソンモービル 10

※kabuテーブルは米国企業の時価総額の上位8社をまとめた表で
portfolioテーブルは保有している銘柄をまとめた表です。

副問い合せの結果が単一行の場合の書き方

副問合せの結果が単一行になる場合は=演算子を用いる。

例題1:「株テーブルの中で時価総額が最大となる銘柄名とその時価総額を取得する」
この例題の結果を取得するSQL文は以下のとおり。

SELECT stockName,cap
FROM kabu
WHERE cap = ( SELECT MAX(cap) FROM kabu )

解説

①.内側のSELECT文が実行され、表内の最大となる時価総額「942231955」を取得する。

時価総額(千ドル)
cap
942231955

②.3行目のWHERE cap = ( SELECT MAX(cap) FROM kabu )だった箇所が
WHERE cap = 942231955と等価になる。

③.SELECT stockName,cap FROM kabu WHERE cap = 942231955 が実行され例題の結果を取得することができる。
<実行結果>

銘柄名
stockName
時価総額(千ドル)
cap
アップル 942231955

副問い合せの結果が複数行になる場合の書き方

副問合せの結果が複数行になる場合は=ではなく、IN演算子,ANY演算子,ALL演算子を用いる。

例題2:「株テーブルから、ポートフォリオテーブルに存在する全tickerの時価総額を取得する。」
この例題の結果を取得するSQL文は以下のとおり。

SELECT ticker,stockName,cap
FROM kabu

WHERE ticker IN (SELECT ticker FROM portfolio)

解説

①.内側のSELECT文が実行され、portfolioテーブルのtickerを取得する。

ティッカー
ticker
AMZN
NFLX
XOM

②.3行目のWHERE ticker IN ( SELECT ticker FROM portfolio )だった箇所が
WHERE ticker IN ('AMZN','NFLX','XOM')と等価になる。

③.SELECT ticker,stockName,cap FROM kabu WHERE ticker IN ('AMZN','NFLX','XOM')
が実行され例題の結果を取得することができる。
<実行結果>

ティッカー
ticker
銘柄名
stockName
時価総額(千ドル)
cap
AMZN アマゾン 817117254
XOM エクソンモービル 353948559

※portfolioテーブル上に存在するNFLX(ネットフリックス)は、株テーブル上には存在しないため抽出されない。

副問い合せの結果が表形式の場合の書き方

INSERT文やUPDATE文などで真価を発揮する。

例題3:「例題2の結果を別テーブルへINSERTする。」
この例題を満たすSQL文は以下のとおり。

INSERT INTO kabu (ticker,stockName,cap)
SELECT ticker,stockName,cap
FROM kabu
WHERE ticker IN (SELECT ticker FROM portfolio)
解説

①.内側のSELECT文が実行され、例題2の結果を取得する。

ティッカー
ticker
銘柄名
stockName
時価総額(千ドル)
cap
AMZN アマゾン 817117254
XOM エクソンモービル 353948559

②.取得した結果をINSERTする。

相関副問い合せ(相関サブクエリ)の書き方

相関サブクエリとは、副問い合せの形になっていてかつ、内側から外側の表や列を参照する記法のこと。

通常のサブクエリが内側のSELECT文の結果に基づき外側のSQLを実行するのに対し、
相関サブクエリは外側の実行結果1件に対して内側のSELCT文が実行される

上記のことから相関サブクエリはループのような振る舞いをするため、パフォーマンスが悪くなる傾向にある。

例題4:「株テーブルに、セクター別の最高時価総額も併せて表示する。」
この例題の結果を取得するSQL文は以下のとおり。

SELECT ticker,
   stockName,
   cap,
    sector,    
   (SELECT MAX(cap)
   FROM kabu AS k2
   WHERE k1.sector = k2.sector) AS MAX_cap_sector
FROM kabu AS k1
ORDER BY cap DESC

解説

①.外側の1件に対して内側のSELCT文を実行する。
【ループ1回目】
株テーブルの1件目はアップルです。
アップルのセクターはテクノロジーのため、このときの内側のSELECT文は以下のように置き換わります。

SELECT MAX(cap)
    FROM kabu k2
    WHERE 'テクノロジー' = k2.sector

このSELECT文の結果は下記になるため、アップルに紐づくセクター別の最高時価総額は’942231955’になります。

MAX(CAP)
942231955

--
【ループ2回目】
株テーブルの2件目はアマゾンです。
アマゾンのセクターは生活必需品のため、このときの内側のSELECT文は以下のように置き換わります。

SELECT MAX(cap)
    FROM kabu k2
    WHERE '生活必需品' = k2.sector

このSELECT文の結果は下記になるため、アマゾンに紐づくセクター別の最高時価総額は’817117254’になります。

MAX(CAP)
817117254

【ループ3回目以降】
株テーブルの3件目のマイクロソフトの場合も、1件目のアップルや2件目のアマゾンの場合と同様に抽出していきます。
これを株テーブルの件数分繰り返すと以下の結果になります。
<実行結果>

ティッカー
ticker
銘柄名
stockName
時価総額(千ドル)
cap
市場
market
セクター
sector
セクター別最高時価総額
MAX_cap_sector
AAPL アップル 942231955 NASDAQ テクノロジー 942231955
AMZN アマゾン 817117254 NASDAQ 生活必需品 817117254
MSFT マイクロソフト 780843362 NASDAQ テクノロジー 942231955
BABA アリババ 526175666 NASDAQ 生活必需品 817117254
FB フェイスブック 453576433 NASDAQ コミュニケーション 453576433
GOOG グーグル 391130080 NASDAQ コミュニケーション 453576433
JPM JPモルガン 378304764 NYSE ファイナンシャル 378304764
XOM エクソンモービル 353948559 NYSE エネルギー 353948559