SELECT の例 (Transact-SQL)

[アーティクル]
06/18/2013

ここでは、SELECT ステートメントの使用例を紹介します。

A. SELECT を使用して行および列を取得する

3 つのプログラム例を次に示します。最初の例では、 AdventureWorks2012 データベース内の Product テーブルから、WHERE 句を指定せずにすべての行を返し、また * を使用してすべての列を返しています。

USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks2012;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO

この例では、 AdventureWorks2012 データベース内の Product テーブルから、WHERE 句を指定せずにすべての行と、一部の列 (Name、ProductNumber、ListPrice) のみを返しています。さらに、列ヘッダーが追加されています。

USE AdventureWorks2012;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
ORDER BY Name ASC;
GO

この例では、製品ラインが R で、製造所要日数が 4 日未満の Product の行のみを返しています。

USE AdventureWorks2012;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
WHERE ProductLine = 'R' 
AND DaysToManufacture < 4
ORDER BY Name ASC;
GO

B. 列ヘッダーおよび計算処理と共に SELECT を使用する

次の例では、Product テーブルのすべての行を返します。最初の例では、各製品の売上合計と売上割引を返します。 2 番目の例では、製品ごとの合計収入が計算されます。

USE AdventureWorks2012;
GO
SELECT p.Name AS ProductName, 
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;
GO

次の例は、販売注文ごとに各製品の収入を計算するクエリです。

USE AdventureWorks2012;
GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName 
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID 
ORDER BY ProductName ASC;
GO

C. DISTINCT を SELECT と共に使用する

次の例では、DISTINCT を使って重複しているタイトルを取得しないようにしています。

USE AdventureWorks2012;
GO
SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;
GO

D. SELECT INTO を使用してテーブルを作成する

次の最初の例では、tempdb 内に #Bicycles という一時テーブルを作成します。

USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT * 
INTO #Bicycles
FROM AdventureWorks2012.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

2 番目の例では、NewProducts という名前のパーマネントテーブルを作成します。

USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
    DROP TABLE dbo.NewProducts;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25 
AND ListPrice < $100;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO

E. 相関サブクエリを使用する

次の例では、EXISTS キーワードと IN キーワードを使用した意味的に等しいクエリと、それらの違いを示します。いずれも、製品モデルが長袖ジャージで、Product テーブルと ProductModel テーブルの間で ProductModelID 番号が一致する各製品名の 1 つのインスタンスを取得する有効なサブクエリの例です。

USE AdventureWorks2012;
GO
SELECT DISTINCT Name
FROM Production.Product AS p 
WHERE EXISTS
    (SELECT *
     FROM Production.ProductModel AS pm 
     WHERE p.ProductModelID = pm.ProductModelID
           AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');
GO

-- OR

USE AdventureWorks2012;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
    (SELECT ProductModelID 
     FROM Production.ProductModel
     WHERE Name LIKE 'Long-Sleeve Logo Jersey%');
GO

次の例では、相関または繰り返しサブクエリ内で IN を使用しています。これは、外側のクエリによって値が決まるクエリです。このクエリは、外側のクエリが選択する行に対して 1 回ずつ、繰り返し実行されます。このクエリは、SalesPerson テーブルのボーナス額が 5000.00 で、従業員の ID 番号が Employee テーブルと SalesPerson テーブルで一致する各従業員の姓名のインスタンスを 1 つ取得します。

USE AdventureWorks2012;
GO
SELECT DISTINCT p.LastName, p.FirstName 
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN
    (SELECT Bonus
     FROM Sales.SalesPerson AS sp
     WHERE e.BusinessEntityID = sp.BusinessEntityID);
GO

上記のステートメントのサブクエリは、外側のクエリから独立して評価できません。このサブクエリは Employee.EmployeeID の値を必要としますが、この値は、SQL Server データベースエンジンが調べる Employee の行によって変化します。

相関サブクエリは、外側のクエリの HAVING 句でも使えます。この例では、表示価格がモデルの平均値の 2 倍以上の製品モデルを検索します。

USE AdventureWorks2012;
GO
SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
    (SELECT AVG(p2.ListPrice)
     FROM Production.Product AS p2
     WHERE p1.ProductModelID = p2.ProductModelID);
GO

この例では、2 つの相関サブクエリを使って、特定の製品を販売した従業員の名前を検索します。

USE AdventureWorks2012;
GO
SELECT DISTINCT pp.LastName, pp.FirstName 
FROM Person.Person pp JOIN HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN 
(SELECT SalesPersonID 
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN 
(SELECT SalesOrderID 
FROM Sales.SalesOrderDetail
WHERE ProductID IN 
(SELECT ProductID 
FROM Production.Product p 
WHERE ProductNumber = 'BK-M68B-42')));
GO

F. GROUP BY を使用する

次の例は、データベース内の各販売注文の合計を検索します。

USE AdventureWorks2012;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO

GROUP BY 句があるため、各販売注文につき 1 行だけが返され、この行にその販売注文のすべての売上合計が含まれます。

G. GROUP BY を複数のグループと共に使用する

次の例では、平均価格および今年に入ってからの売り上げの合計を、製品 ID と特価品 ID でグループ化して返します。

USE AdventureWorks2012;
GO
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], 
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID;
GO

H. GROUP BY と WHERE を使用する

次の例では、表示価格が $1000 より多い行だけを取得した後、結果をグループ化します。

USE AdventureWorks2012;
GO
SELECT ProductModelID, AVG(ListPrice) AS [Average List Price]
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID;
GO

I. 1 つの式と共に GROUP BY を使用する

次の例では、式によってグループ化します。式に集計関数が含まれない限り、式によってグループ化することができます。

USE AdventureWorks2012;
GO
SELECT AVG(OrderQty) AS [Average Quantity], 
NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO

J. ORDER BY と共に GROUP BY を使用する

次の例では、それぞれの製品の種類別の平均価格を求め、その結果を平均価格の順序で表示しています。

USE AdventureWorks2012;
GO
SELECT ProductID, AVG(UnitPrice) AS [Average Price]
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);
GO

K. HAVING 句を使用する

最初の例では、HAVING 句を集計関数と共に使用しています。 SalesOrderDetail テーブルの行を製品 ID 別にグループ化し、平均注文数が 5 以下の製品を除外しています。 2 番目の例では、HAVING 句を集計関数なしで使用しています。

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

次のクエリでは、HAVING 句の中で LIKE 句を使用しています。

USE AdventureWorks2012 ;
GO
SELECT SalesOrderID, CarrierTrackingNumber 
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO

L. HAVING と GROUP BY を使用する

次の例では、1 つの SELECT ステートメントの中で GROUP BY 句、HAVING 句、WHERE 句、および ORDER BY 句を使用しています。これによって、$25 より高く平均注文数量が 5 未満の製品を除外した、グループとサマリー値が作成されます。この結果は ProductID 別にまとめられます。

USE AdventureWorks2012;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

M. HAVING を SUM および AVG と共に使用する

次の例では、SalesOrderDetail テーブルから、注文合計額が $1000000.00 を超え、かつ平均注文数が 3 未満の製品を製品 ID 別にグループ化します。

USE AdventureWorks2012;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3;
GO

売上合計が $2000000.00 を超える製品を検索するには、このクエリを使用します。

USE AdventureWorks2012;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;
GO

各製品の集計に最低 1,500 の品目が含まれているようにするには、HAVING COUNT(*) > 1500 を使って、1500 未満の合計を返す製品を除外します。クエリは次のようになります。

USE AdventureWorks2012;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO

N. INDEX オプティマイザーヒントを使用する

次の例では、INDEX オプティマイザーヒントの使用方法を 2 とおり示します。最初の例では、オプティマイザーで非クラスター化インデックスを使用し、テーブルから行を取得しています。2 番目の例では、index = 0 を使ってテーブルスキャンを実行しています。

USE AdventureWorks2012;
GO
SELECT pp.FirstName, pp.LastName, e.NationalIDNumber
FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))
JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks2012;
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS pp
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

M. OPTION ヒントと GROUP ヒントを使用する

次の例は、GROUP BY 句と共に OPTION (GROUP) 句を使用する方法を示します。

USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

O. UNION クエリヒントを使用する

次の例では、MERGE UNION クエリヒントを使用します。

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

P.単純な UNION を使用する

次の例では、結果セットに ProductModel テーブルと Gloves テーブルの ProductModelID 列と Name 列の内容が含まれています。

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

-- Here is the simple union.
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

Q. UNION と共に SELECT INTO を使用する

この例では、2 番目の SELECT ステートメントの INTO 句で、ProductModel および Gloves テーブルの指定された列のユニオンの最終的な結果セットを ProductResults という名前のテーブルに格納することを指定します。 Gloves テーブルは、最初の SELECT ステートメントで作成されます。

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

SELECT ProductModelID, Name 
FROM dbo.ProductResults;

R. ORDER BY 句を指定した 2 つの SELECT ステートメントで UNION 句を使用する

UNION 句で使用するある種のパラメーターの順序には重要な意味があります。次の例では、出力時に列名を変更する 2 つの SELECT ステートメントでの UNION の誤った使用法と正しい使用法を示しています。

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO

/* INCORRECT */
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO

/* CORRECT */
USE AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

S. 3 つの SELECT ステートメントで UNION を使用して、ALL とかっこの効果を示す

次の例では、UNION を使用して 3 つのテーブルのクエリ結果を結合します。これらのテーブルはすべて同じ 5 行のデータで構成されます。最初の例では、UNION ALL を使用して、重複するレコードも含めて 15 行すべてを返します。 2 番目の例では、ALL を指定せずに UNION を使用して、3 つの SELECT ステートメントの結果を結合したものから重複する行を削除し、5 行を返します。

3 番目の例では、最初の UNION と共に ALL を使用し、ALL を使用していない 2 番目の UNION をかっこで囲んでいます。 2 番目の UNION はかっこで囲まれているので、最初に処理されます。また、ALL オプションを使用せずに重複を削除するので、5 行を返します。これらの 5 行は、UNION ALL キーワードを使用して最初の SELECT の結果と結合されます。これによって 2 組の 5 行の間での重複が削除されることはありません。最終的な結果は 10 行になります。

USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeOne
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeTwo
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeThree
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName ,JobTitle
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeOne
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree
);
GO

SELECT の例 (Transact-SQL)

A. SELECT を使用して行および列を取得する

B. 列ヘッダーおよび計算処理と共に SELECT を使用する

C. DISTINCT を SELECT と共に使用する

D. SELECT INTO を使用してテーブルを作成する

E. 相関サブクエリを使用する

F. GROUP BY を使用する

G. GROUP BY を複数のグループと共に使用する

H. GROUP BY と WHERE を使用する

I. 1 つの式と共に GROUP BY を使用する

J. ORDER BY と共に GROUP BY を使用する

K. HAVING 句を使用する

L. HAVING と GROUP BY を使用する

M. HAVING を SUM および AVG と共に使用する

N. INDEX オプティマイザーヒントを使用する

M. OPTION ヒントと GROUP ヒントを使用する

O. UNION クエリヒントを使用する

P.単純な UNION を使用する

Q. UNION と共に SELECT INTO を使用する

R. ORDER BY 句を指定した 2 つの SELECT ステートメントで UNION 句を使用する

S. 3 つの SELECT ステートメントで UNION を使用して、ALL とかっこの効果を示す

関連項目

参照

その他のリソース

SELECT の例 (Transact-SQL)

A. SELECT を使用して行および列を取得する

B. 列ヘッダーおよび計算処理と共に SELECT を使用する

C. DISTINCT を SELECT と共に使用する

D. SELECT INTO を使用してテーブルを作成する

E. 相関サブクエリを使用する

F. GROUP BY を使用する

G. GROUP BY を複数のグループと共に使用する

H. GROUP BY と WHERE を使用する

I. 1 つの式と共に GROUP BY を使用する

J. ORDER BY と共に GROUP BY を使用する

K. HAVING 句を使用する

L. HAVING と GROUP BY を使用する

M. HAVING を SUM および AVG と共に使用する

N. INDEX オプティマイザー ヒントを使用する

M. OPTION ヒントと GROUP ヒントを使用する

O. UNION クエリ ヒントを使用する

P.単純な UNION を使用する

Q. UNION と共に SELECT INTO を使用する

R. ORDER BY 句を指定した 2 つの SELECT ステートメントで UNION 句を使用する

S. 3 つの SELECT ステートメントで UNION を使用して、ALL とかっこの効果を示す

関連項目

参照

その他のリソース

N. INDEX オプティマイザーヒントを使用する

O. UNION クエリヒントを使用する