第 4 章 「SELECT ステートメント (2)」 ～ 初めて学ぶ Transact-SQL ～

NEC

Eラーニング事業部
鈴木 智行

2003 年 3 月 5 日

目次

1.　復習問題の解答例
2.　結合
3.　サブクエリ
4.　復習問題

1.　復習問題の解答例

1.復習問題の解答例

　第3章の復習問題の解答例は以下の通りですが、必ずしも一字一句同じである必要はありません。ロジックはいろいろありますので、意図したとおりの正しい結果が返ってくれば正解と考えてください。

問題1

　女性社員全員の社員番号と社員名を表示するためのTransact-SQL文を記述しなさい

use 演習    
    select 社員番号,社員名 from 社員    
    where 性別 = N'女'    

（解説）女性社員は性別列で’女’と識別されています

問題2

　新入社員全員の社員名を表示するためのTransact-SQL文を記述しなさい

use 演習    
    select 社員名 from 社員    
    where 所属部署 is NULL（

解説）新入社員の所属部署にはNULL(未定)が設定されています。SET ANSI_NULLSの設定を意識したくない場合はIS NULL、IS NOT NULLを使用してください。

問題3

　社員番号が3番から5番までの社員番号と社員名を表示するためのTransact-SQL文を記述しなさい

use 演習    
    select 社員番号,社員名 from 社員    
    where 社員番号 between 3 and 5

（解説）範囲検索にはbetweenキーワードを使用します。betweenは両端の値を含みます。

問題4

　所属部署が総務部第一課、総務部第二課である社員名を表示するためのTransact-SQL文を記述しなさい

use 演習    
    select 社員名 from 社員    
    where 所属部署 LIKE N'総務部%'

（解説）総務部には第一課と第二課しかないのでワイルドカードが使用できます。

問題5

　所属部署と各所属部署に在籍する社員の人数を表示するためのTransact-SQL文を記述しなさい、ただし結果セットでの社員の人数にあたる列名は「在籍人数」という列名にし、新入社員はNULLの部署の在籍人数に数えること

use 演習    
    select 所属部署,count(*) as 在籍人数 from 社員    
    group by 所属部署     

（解説）count(*)はNULL値もカウントします。count(所属部署)はNULL値をカウントしません。また新しい列名をつけるためにASキーワードを使用します。

問題6

　3人以上在籍する所属部署と所在地を表示するためのTransact-SQL文を記述しなさい、ただし新入社員は数にいれないこと

use 演習    
    select 所属部署,所在地 from 社員    
    group by 所属部署,所在地    
    having count(所属部署) > 3

（解説）ある所属部署に3人以上在籍するかどうかはグループ化した後で判断します。このような場合にhaving句を使用します。

問題7

　在籍人数が多い順に所属部署を並び替え、第2位までの所属部署と在籍する社員の人数を表示するためのTransact-SQL文を記述しなさい、ただし結果セットでの社員の人数にあたる列名は「在籍人数」という列名にし、同点2位も含み、新入社員は数にいれないこと

use 演習    
    select top 2 with ties 所属部署,count(所属部署) as 在籍人数 from 社員    
    group by 所属部署    
    order by 在籍人数 desc

（解説）同点を含む場合はtop句にwith ties句を加えます。また並び替えの既定値は昇順なので、降順の場合は必ずdescキーワードを指定してください。

2.　結合

　SQL Serverではデータベース内に通常ユーザーが使用するテーブルが複数存在します。1つのテーブルだけなら非常に簡単に取り扱えそうに思えますが、データが重複して更新した際に矛盾が発生したり、アクセスが集中してスループットが低下しパフォーマンスが悪くなるなどの弊害が発生します。そのような弊害を未然に防ぐためにデータベースの設計段階ではある程度の正規化を行ってテーブルを複数作成し、テーブル間で参照関係（第1章2.リレーショナルデータベース参照）を設定します。

この場合ある商品名とその商品を製造した会社名を求めるためには、商品テーブルと製造会社テーブルの参照関係を用いて2つのテーブルから結果を得る必要があります。Transact-SQLでは2つのテーブルを結合することでこれを実現します。

　結合には内部結合、外部結合、クロス結合の3種類があり、この他にも特別な結合方法として自己結合があります。一般的な構文は

select 選択リスト from テーブル名 1 ×× join テーブル名 2 on 結合条件  

であり、joinキーワードで結合の種類を、onキーワードで結合条件を指定します。

もし3つ以上のテーブルからデータを取得したい場合には、2つのテーブル結合を繰り返して（ステートメントではjoinキーワードとonキーワードを繰り返して）対応します。

2-1 別名

　結合を使用する際には、通常複数のテーブルを使用します。この時選択リストに列を表示したり結合条件を指定するときに、もし両方のテーブルに同じ列名があった場合どうすればいいでしょうか？SQL Serverでは同じ列名があった場合にはどちらのテーブルに所属する列であるかをテーブル名で修飾して明示しなくてはいけません。

テーブル名.列名

　しかしテーブル名が長い場合、テーブル名で修飾するのは面倒です。そこで入力しやすく覚えやすいようにテーブル名には別名を指定することができます。別名を指定する場合にはfrom句でテーブルを指定する際にasキーワードを使用します。

テーブル名 as テーブル名の別名

上記のように指定すれば、テーブル名の代わりにテーブル名の別名を使用してTransact-SQL文を構築することができます。

　ただし別名は必須ではありませんし、列名が一意に識別できる場合にはテーブル名で修飾する必要もありません。

2-2 内部結合

　内部結合は2つのテーブルを比較し、結合条件に一致した行だけを返す結合方法です。内部結合にはinner joinキーワードを使います。例えば顧客テーブルと注文テーブルから顧客名に対する商品コード、数量、yyyy/mm/dd形式に整形された注文日を取得する場合は以下のTransact-SQL文になります（画面2-2-1）。

画面 2-2-1 内部結合には inner join キーワードを使用する

　内部結合はSQL Server 2000の既定なのでinner joinキーワードはinnerを省略しても構いません。

2-3 外部結合

外部結合は2つのテーブルを比較し結合条件に一致した行に加え、指定したいずれかのテーブルの一致しない行を返す結合方法です。テーブルの指定にはfrom句でのテーブルの順番が重要になります。from句で最初に記述したテーブルを左、次に記述したテーブルを右とみなし、最初に記述したテーブルの一致しない行を返したい場合は左外部結合（left outer joinキーワード）、次に記述したテーブルの一致しない行を返したい場合は右外部結合（right outer joinキーワード）を使用します。例えば画面2-2-1の条件に加え顧客テーブルの一致しない行も取得する場合には以下のTransact-SQL文になります（画面2-3-1）。

画面 2-3-1 左内部結合には left outer join キーワードを使用する

この場合、結合条件に一致しない行にはNULLが表示されますが、“注文を1回もしていない顧客も全て表示したい”ような場合に外部結合は非常に有効な手段となりえます。

2-4 クロス結合

クロス結合は2つのテーブルの行の全ての組み合わせを返す結合方法です。一般的に直積とよばれている手法で、cross joinキーワードを使用します。例えば顧客テーブルと商品テーブルから顧客名に加え、顧客が購入できる商品名を表示するには以下のTransact-SQL文を使います（画面2-4-1）。

画面 2-4-1 クロス結合には cross join キーワードを使用する

クロス結合は全ての組み合わせなので、onキーワードはありません。一方のテーブルがm行、もう一方のテーブルがn行の場合は結果はm×n行になります。

2-5 自己結合

　結合の中には自分自身のテーブル内に結合する共通列をもつ場合があります。例えばこのコラムで利用している販売データベースの製造会社テーブルには、親会社コードという列があり、同じテーブルの会社コードと共通の意味をもつ情報になっています。したがって会社名に加えて親会社名を取得したい場合には自分自身と結合する必要があり、これを自己結合といいます。上記の取得を実現するためには以下のTransact-SQL文を使います（画面2-5-1）。

画面 2-5-1 自己結合の例

　自己結合は自分自身との結合なのでテーブル名が同じです。しかしSQL Serverは結合するテーブルを区別しなければいけないため、別名が必要になります。別名を指定しないとエラーになります（画面2-5-2）。

画面 2-5-2 自己結合では別名が必須

3.　サブクエリ

　SELECTステートメントの結果は単一の値、単一列の値リスト、リレーショナル形式のテーブルである場合がほとんどであり、その結果を普通のデータとみなして他のステートメントに利用することができます。Transact-SQLではこれを実現するために、SELECT、INSERT、DELETE、UPDATEのステートメント内にSELECTステートメントを入れ子にして記述します。この入れ子されたSELECTステートメントをサブクエリといいます。またサブクエリのことを内部クエリ、サブクエリをもっているクエリのことを外部クエリと呼ぶこともあります。サブクエリは手順を1つ1つ分解して実装できるので、複雑なロジックを考える際に非常に役立ちます。

3-1 単一の値を返すサブクエリ

　単一の値（スカラ値と呼ぶ場合もある）を返すサブクエリは式（第1章 5.2式参照）を使用する場所であれば、どこにでも記述できます。例えば商品情報テーブルから商品コードP00002の利益(販売単価-原価)より多い商品コードを求める場合には以下のTransact-SQL文を使います（画面3-1-1）。

画面 3-1-1 単一の値を返すサブクエリを使った例

3-2単一列の値リストを返すサブクエリ

　単一列の値リストを返すサブクエリはINキーワードと併用して使用します。例えば注文テーブルと商品テーブルから2003/01/11に注文があった商品名を求める場合には以下のTransact-SQL文を使います（画面3-2-1）。

画面 3-2-1 単一列の値リストを返すサブクエリを使った例

3-3 テーブルを返すサブクエリ

　テーブル（派生テーブルと呼ぶ場合もある）を返すサブクエリはfrom句で使用します。派生テーブルには別名をつけて利用してください。

3-4 相関サブクエリ

　内部クエリの記述において外部クエリで指定されたテーブルを扱っているクエリのことを相関サブクエリといいます。この場合、内部クエリだけでは結果が導きだせません。外部クエリで指定したテーブルから1行ごとに列の値を内部クエリに渡して評価します。その作業を繰り返し行うことで最終的な外部クエリの結果を得ることができます。例えば注文テーブルと顧客テーブルから商品コードP00001を合計10以上注文した顧客名一覧を求める場合には以下のTransact-SQL文を使います（画面3-4-1）。

画面 3-4-1 相関サブクエリを使った例

上記のステートメントは以下の動作をとります。

1)外部クエリが顧客テーブルから1件目の顧客コード(P100000001)を内部クエリに提供

2)内部クエリは外部クエリから渡された顧客コード(P100000001)を使用し、内部クエリの結果（この場合は条件を満たすものがない）を外部クエリに提供

3)外部クエリは内部クエリから渡された結果を使用し評価（この場合は条件を満たさない）

4)外部クエリが顧客テーブルから2件目の顧客コード(P100000002)を......以下繰り返し

　相関サブクエリはグループ化やhaving句、join句などの別な方法でも記述が可能な場合があります。

4.　復習問題

では、今までの復習をしてみましょう。今回の問題はコラムで使用している販売データベースを利用します。

問題1

　商品テーブルと製造会社テーブルから商品名とその商品を製造した会社名を取得するためのTransact-SQL文を記述しなさい

画面 4-1 問題 1 の結果

問題2

　顧客テーブルと商品テーブルと注文テーブルから顧客名に対する商品名、数量、yy/mm/dd形式に整形された注文日を取得するためのTransact-SQL文を記述しなさい、ただし商品は注文されてなくても表示すること

画面 4-2 問題 2 の結果

問題3

　商品テーブルと商品情報テーブルと注文テーブルから商品名とその商品の総売上と総利益を取得するためのTransact-SQL文を記述しなさい、ただし売上は販売単価*数量で列名は「総売上」という名前、利益は(販売単価-原価)*数量で列名は「総利益」という名前とし、総利益が3000以下の商品は除外すること

画面 4-3 問題 3 の結果

問題4

　顧客テーブルと注文テーブルから1回の注文で数量を30以上注文したことがある顧客名一覧を出力するためのTransact-SQL文をサブクエリで記述しなさい

画面 4-4 問題 4 の結果

問題5

　顧客テーブルと注文テーブルから今までに注文を3回以上行ったことがある顧客名一覧を出力するためのTransact-SQL文をサブクエリで記述しなさい

画面 4-5 問題 5 の結果

次回は更新系(INSERT,DELETE,UPDATE)ステートメントについてご紹介します

鈴木 智行: NEC Eラーニング事業部に所属。入社以来、インストラクタとして教育業務に従事。汎用機、UNIX を経て、1994 年より マイクロソフト認定トレーナー (MCT) として、管理者向け教育を担当。SQL Server は 4.21a から携わっており、現在は主に SQL Server 2000 に関わるデータベース教育を中心に担当。 Windows 2000 および SQL Server 2000 での MCSE,MCDBA を取得しており、情報処理技術者試験のテクニカルエンジニア（データベース）も取得済。 SQL Server の優位性をアピールできるように Oracle Gold も取得した。今後 Oracle Plutinum を取得予定し、日々データベースを極めることに努力している。