SELECT の例 (Transact-SQL)
ここでは、SELECT ステートメントの使用例を紹介します。
A. SELECT を使用して行および列を取得する
3 つのプログラム例を次に示します。最初の例では、AdventureWorks データベース内の Product テーブルから、WHERE 句を指定せずにすべての行を返し、また * を使用してすべての列を返しています。
USE AdventureWorks;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO
この例では、AdventureWorks データベース内の Product テーブルから、WHERE 句を指定せずにすべての行と、一部の列 (Name、ProductNumber、ListPrice) のみを返しています。さらに、列ヘッダーが追加されています。
USE AdventureWorks;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
ORDER BY Name ASC;
GO
この例では、製品ラインが R で、製造所要日数が 4 日未満の Product の行のみを返しています。
USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title;
GO
D. SELECT INTO を使用してテーブルを作成する
次の最初の例では、tempdb 内に #Bicycles という一時テーブルを作成します。このテーブルを使用するには、常に表示されている名前を正確に指定する必要があります。これにはシャープ記号 (#) も含まれます。
USE tempdb;
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT *
INTO #Bicycles
FROM Production.Product
WHERE ProductNumber LIKE 'BK%';
GO
以下に結果セットを示します。
name
------------------------------
#Bicycles_____________________
2 番目の例では、NewProducts という名前のパーマネント テーブルを作成します。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
GO
SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100;
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
以下に結果セットを示します。
name
------------------------------
NewProducts
(1 row(s) affected)
E. 相関サブクエリを使用する
次の例では、EXISTS キーワードと IN キーワードを使用した意味的に等しいクエリと、それらの違いを示します。いずれも、製品モデルが長袖ジャージで、Product テーブルと ProductModel テーブルの間で ProductModelID 番号が一致する各製品名の 1 つのインスタンスを取得する有効なサブクエリの例です。
USE AdventureWorks;
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 = 'Long-sleeve logo jersey');
GO
-- OR
USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey');
GO
次の例では、相関または繰り返しサブクエリ内で IN を使用しています。これは、外側のクエリによって値が決まるクエリです。このクエリは、外側のクエリが選択する行に対して 1 回ずつ、繰り返し実行されます。このクエリは、SalesPerson テーブルのボーナス額が 5000.00 で、従業員の ID 番号が Employee テーブルと SalesPerson テーブルで一致する各従業員の姓名のインスタンスを 1 つ取得します。
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson AS sp
WHERE e.EmployeeID = sp.SalesPersonID);
GO
上記のステートメントのサブクエリは、外側のクエリから独立して評価できません。このサブクエリは Employee.EmployeeID の値を必要としますが、この値は、SQL Server データベース エンジンが調べる Employee の行によって変化します。
相関サブクエリは、外側のクエリの HAVING 句でも使えます。この例では、表示価格がモデルの平均値の 2 倍以上の製品モデルを検索します。
USE AdventureWorks;
GO
SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
(SELECT 2 * AVG(p2.ListPrice)
FROM Production.Product AS p2
WHERE p1.ProductModelID = p2.ProductModelID);
GO
この例では、2 つの相関サブクエリを使って、特定の製品を販売した従業員の名前を検索します。
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID 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 AdventureWorks;
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 AdventureWorks
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO
次のクエリでは、HAVING 句の中で LIKE 句を使用しています。
USE AdventureWorks ;
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO
N. COMPUTE BY でグループ合計を計算する
ここでは、COMPUTE BY を使った 2 つのプログラム例を示します。最初の例では、1 つの COMPUTE BY に 1 つの集計関数を使用し、2 番目の例では 1 つの COMPUTE BY に 2 つの集計関数を使用しています。
この例では、各種類の製品ごとに、価格が $5.00 未満の注文の合計を計算します。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal) BY ProductID;
GO
このクエリでは、製品の種類と、単価が $5.00 未満の製品の注文合計額を取得します。この COMPUTE BY 句では 2 つの異なる集約関数を使用します。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID;
GO
O. BY を伴わない COMPUTE を使用して総計値を計算する
COMPUTE キーワードは、総計や総数などを作成するために、BY なしで使うことができます。
次の例は、$2.00 未満のすべての種類の製品の価格および前払い金の総計を検索します。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
COMPUTE BY および BY なしの COMPUTE は同じクエリの中で使うことができます。次のクエリは、製品の種類別に注文数量の合計と行の合計を返し、注文数量と行の合計の総計を計算します。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
P. すべての行で計算済みの総計を計算する
次の例では、選択リストに指定されているのは 3 列のみで、すべての注文数量およびすべての行の合計に基づいた合計値が結果の終わりに表示されます。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
Q. 複数の COMPUTE 句を使用する
次の例では、単価が $5 未満のすべての注文の価格合計を製品 ID と注文数量別に検索し、$5 未満のすべての注文の価格合計を製品 ID 別のみで検索します。複数の COMPUTE 句を含めることにより、同じステートメントの中で異なる集計関数を使うことができます。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, OrderQty, LineTotal
COMPUTE SUM(LineTotal) BY ProductID, OrderQty
COMPUTE SUM(LineTotal) BY ProductID;
GO
R. GROUP BY と COMPUTE を比較する
最初の例は、COMPUTE 句を使用して、製品単価が $5.00 未満のすべての注文の合計を製品の種類別に計算しています。2 番目の例では、GROUP BY だけを使って同じ集計情報を生成しています。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID;
GO
GROUP BY を使った 2 番目のクエリを次に示します。
USE AdventureWorks;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID
ORDER BY ProductID;
GO
S. GROUP BY 句、COMPUTE 句、および ORDER BY 句を含む SELECT を使用する
次の例は、単価が $5 未満の注文のみを返し、製品と総計別に行の合計金額を計算します。選択リストにすべての計算列が指定されています。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal));
GO
T. INDEX オプティマイザ ヒントを使用する
次の例では、INDEX オプティマイザ ヒントの使用方法を 2 とおり示します。最初の例では、オプティマイザで非クラスタ化インデックスを使用し、テーブルから行を取得しています。2 番目の例では、index = 0 を使ってテーブル スキャンを実行しています。
-- Use the specifically named INDEX.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee AS e WITH (INDEX(IX_Employee_ManagerID))
JOIN Person.Contact AS c on e.ContactID = c.ContactID
WHERE ManagerID = 3;
GO
-- Force a table scan by using INDEX = 0.
USE AdventureWorks;
GO
SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE LastName = 'Johnson';
GO
U. OPTION ヒントと GROUP ヒントを使用する
次の例は、GROUP BY 句と共に OPTION (GROUP) 句を使用する方法を示します。
USE AdventureWorks;
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
V. UNION クエリ ヒントを使用する
次の例では、MERGE UNION クエリ ヒントを使用します。
USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
W. 単純な UNION を使用する
次の例では、結果セットに ProductModel テーブルと Gloves テーブルの ProductModelID 列と Name 列の内容が含まれています。
USE AdventureWorks;
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 AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
X. UNION と共に SELECT INTO を使用する
この例では、2 番目の SELECT ステートメントの INTO 句で、ProductModel および Gloves テーブルの指定された列のユニオンの最終的な結果セットを ProductResults という名前のテーブルに格納することを指定します。Gloves テーブルは、最初の SELECT ステートメントで作成されます。
USE AdventureWorks;
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 AdventureWorks;
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 *
FROM dbo.ProductResults;
Y. ORDER BY 句を指定した 2 つの SELECT ステートメントで UNION 句を使用する
UNION 句で使用するある種のパラメータの順序には重要な意味があります。次の例では、出力時に列名を変更する 2 つの SELECT ステートメントでの UNION の誤った使用法と正しい使用法を示しています。
USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
Z. 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 AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE EmployeeThree;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeOne
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeTwo
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeThree
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
-- Union ALL
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeThree
);
GO
関連項目