SQL - コードラボJP

SQL HAVING 句

WHERE キーワードは集計関数と共に使用できないため、SQL に HAVING 句が追加されました。

HAVING 構文

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

デモデータベース

以下は、Northwind サンプルデータベースの “Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

SQL HAVING の例

以下の SQL 文は、各国における顧客数をリストアップし、顧客数が5人以上の国のみを含みます：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

以下の SQL 文は、顧客数が多い順に各国の顧客数をリストアップします（顧客数が5人以上の国のみを含む）：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

デモデータベース

以下は、Northwind サンプルデータベースの “Orders” テーブルからの選択です：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

そして “Employees” テーブルからの選択です：

EmployeeID	LastName	FirstName	BirthDate	Photo	Notes
1	Davolio	Nancy	1968-12-08	EmpID1.pic	教育はBAを含む…
2	Fuller	Andrew	1952-02-19	EmpID2.pic	AndrewはBTSを受け取った…
3	Leverling	Janet	1963-08-30	EmpID3.pic	JanetはBSの学位を持っています…

さらに HAVING の例

以下の SQL 文は、10件以上の注文を登録している従業員をリストアップします：

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

以下の SQL 文は、”Davolio” または “Fuller” の従業員が25件以上の注文を登録しているかをリストアップします：

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

SQL GROUP BY 文

GROUP BY 文は、”国ごとの顧客数を見つける” のように、同じ値を持つ行をまとめて集計行にします。

GROUP BY 文は、一つまたは複数の列で結果セットを集計関数（COUNT()、MAX()、MIN()、SUM()、AVG()）でグループ化する際によく使用されます。

GROUP BY 構文

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

デモデータベース

以下は、Northwind サンプルデータベースの “Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

SQL GROUP BY の例

以下の SQL 文は、各国の顧客数をリストアップします：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

以下の SQL 文は、顧客数が多い順に各国の顧客数をリストアップします：

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

デモデータベース

以下は、Northwind サンプルデータベースの “Orders” テーブルからの選択です：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

そして “Shippers” テーブルからの選択です：

ShipperID	ShipperName
1	Speedy Express
2	United Package
3	Federal Shipping

JOIN と GROUP BY の例

以下の SQL 文は、各配送業者が送信した注文の数をリストアップします：

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

SQL UNION 演算子

UNION 演算子は、2つ以上の SELECT 文の結果セットを結合するために使用されます。

UNION 内のすべての SELECT 文は同じ数の列を持たなければなりません。
列は同様のデータ型を持たなければなりません。
すべての SELECT 文の列は同じ順序でなければなりません。

UNION 構文

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL 構文

UNION 演算子はデフォルトで重複値を選択しません。重複値を許可するには、UNION ALL を使用します：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注：結果セットの列名は通常、最初の SELECT 文の列名と同じです。

デモデータベース

このチュートリアルでは、よく知られた Northwind サンプルデータベースを使用します。

“Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

“Suppliers” テーブルからの選択です：

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	London	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA

SQL UNION の例

以下の SQL 文は、”Customers” テーブルと “Suppliers” テーブルからの都市名（重複を排除した値のみ）を返します：

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

注：顧客またはサプライヤーが同じ都市にある場合、UNION は重複値のみをリストアップします。重複値も選択するには UNION ALL を使用してください！

SQL UNION ALL の例

以下の SQL 文は、”Customers” テーブルと “Suppliers” テーブルからの都市名（重複値も含む）を返します：

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION と WHERE

以下の SQL 文は、”Customers” テーブルと “Suppliers” テーブルからのドイツの都市名（重複を排除した値のみ）を返します：

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SQL UNION ALL と WHERE

以下の SQL 文は、”Customers” テーブルと “Suppliers” テーブルからのドイツの都市名（重複値も含む）を返します：

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

別の UNION の例

以下の SQL 文は、すべての顧客とサプライヤーをリストアップします：

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

上記の “AS Type” に注目してください – これはエイリアスです。SQL エイリアスは、テーブルや列に一時的な名前を付けるために使用されます。エイリアスはクエリの実行中だけ存在します。ここでは一時的な列 “Type” を作成しました。この列は、連絡先が “Customer” または “Supplier” であるかを示します。

SQL Self Join

自己結合（Self Join）は、通常の結合ですが、テーブルが自分自身と結合されます。

Self Join の構文

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

T1 と T2 は同じテーブルの異なるテーブルエイリアスです。

デモデータベース

このチュートリアルでは、よく知られた Northwind サンプルデータベースを使用します。

“Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

SQL Self Join の例

以下の SQL 文は、同じ都市から来た顧客をマッチングします：

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

SQL FULL OUTER JOIN キーワード

FULL OUTER JOIN キーワードは、左側のテーブル（table1）または右側のテーブル（table2）のレコードが一致する場合にすべてのレコードを返します。

Tip: FULL OUTER JOIN と FULL JOIN は同じです。

FULL OUTER JOIN の構文

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

注意: FULL OUTER JOIN は非常に大きな結果セットを返す可能性があります！

デモデータベース

このチュートリアルでは、よく知られた Northwind サンプルデータベースを使用します。

“Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

そして、”Orders” テーブルからの選択です：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

SQL FULL OUTER JOIN の例

以下の SQL 文は、すべての顧客とすべての注文を選択します：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

注意: FULL OUTER JOIN キーワードは、両方のテーブルから一致するレコードをすべて返します。したがって、「Customers」には「Orders」に一致しない行がある場合、または「Orders」には「Customers」に一致しない行がある場合、それらの行もリストされます。

SQL RIGHT JOIN キーワード

RIGHT JOIN キーワードは、右側のテーブル（table2）からすべてのレコードと、左側のテーブル（table1）からの一致するレコードを返します。左側のテーブルに一致するレコードがない場合は、結果には0件のレコードが含まれます。

RIGHT JOIN の構文

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

注意: 一部のデータベースでは RIGHT JOIN は RIGHT OUTER JOIN と呼ばれます。

デモデータベース

このチュートリアルでは、よく知られた Northwind サンプルデータベースを使用します。

“Orders” テーブルからの選択です：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

そして、”Employees” テーブルからの選択です：

EmployeeID	LastName	FirstName	BirthDate	Photo
1	Davolio	Nancy	12/8/1968	EmpID1.pic
2	Fuller	Andrew	2/19/1952	EmpID2.pic
3	Leverling	Janet	8/30/1963	EmpID3.pic

以下の SQL 文は、すべての従業員と、彼らが配置した可能性のある注文を返します：

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

注意: RIGHT JOIN キーワードは、右側のテーブル（Employees）からすべてのレコードを返します。左側のテーブル（Orders）に一致するレコードがなくても、そのレコードが含まれます。

SQL LEFT JOIN キーワード

LEFT JOIN キーワードは、左側のテーブル（table1）からすべてのレコードと、右側のテーブル（table2）からの一致するレコードを返します。右側のテーブルに一致するレコードがない場合は、結果には0件のレコードが含まれます。

LEFT JOIN の構文

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

注意: 一部のデータベースでは LEFT JOIN は LEFT OUTER JOIN と呼ばれます。

デモデータベース

このチュートリアルでは、よく知られた Northwind サンプルデータベースを使用します。

“Customers” テーブルからの選択です：

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

そして、”Orders” テーブルからの選択です：

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

以下の SQL 文は、すべての顧客とその持つ可能性のある注文を選択します：

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

注意: LEFT JOIN キーワードは、左側のテーブル（Customers）からすべてのレコードを返します。右側のテーブル（Orders）に一致するレコードがなくても、そのレコードが含まれます。

INNER JOIN

INNER JOIN キーワードは、両方のテーブルで一致する値を持つレコードを選択します。

Products テーブルの一部を見てみましょう：

ProductID	ProductName	CategoryID	Price
1	Chais	1	18
2	Chang	1	19
3	Aniseed Syrup	2	10

そして、Categories テーブルの一部を見てみましょう：

CategoryID	CategoryName	Description
1	Beverages	Soft drinks, coffees, teas, beers, and ales
2	Condiments	Sweet and savory sauces, relishes, spreads, and seasonings
3	Confections	Desserts, candies, and sweet breads

Products テーブルと Categories テーブルを、両方のテーブルの CategoryID フィールドを使用して結合します：

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

INNER JOIN キーワードは、両方のテーブルで一致するレコードのみを返します。つまり、CategoryID がない製品、または Categories テーブルに存在しない CategoryID を持つ製品は結果に含まれません。

Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

列の命名

SQL 文で列を指定する際には、テーブル名を含めるのが良い習慣です。

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

上記の例では、両方のテーブルに指定された列名が存在しないため、テーブル名を指定しなくても動作します。ただし、SELECT 文に CategoryID を含めようとすると、テーブル名を指定しないとエラーが発生します（CategoryID が両方のテーブルに存在するため）。

JOIN または INNER JOIN

JOIN と INNER JOIN は同じ結果を返します。

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID;

3 つのテーブルを JOIN する

以下の SQL 文は、顧客と配送業者の情報を持つすべての注文を選択します：

以下が Shippers テーブルです：

ShipperID	ShipperName	Phone
1	Speedy Express	(503) 555-9831
2	United Package	(503) 555-3199
3	Federal Shipping	(503) 555-9931

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

SQL ジョイン

JOIN 句は、関連する列を基に、2 つ以上のテーブルから行を結合するために使用されます。

“Orders” テーブルから選択された一部を見てみましょう：

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

次に、”Customers” テーブルから選択された一部を見てみましょう：

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

“Orders” テーブルの “CustomerID” 列が “Customers” テーブルの “CustomerID” 列を参照していることに注意してください。上記の 2 つのテーブルの関係性は “CustomerID” 列です。

次に、以下の SQL 文（INNER JOIN を含む）を作成できます。この SQL 文は、両方のテーブルで一致する値を持つレコードを選択します：

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

この SQL 文は次のような結果を生成します：

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

異なる種類の SQL JOIN

以下は SQL における異なる種類の JOIN の説明です：

(INNER) JOIN: 両方のテーブルで一致するレコードを返します。
LEFT (OUTER) JOIN: 左側のテーブルのすべてのレコードと、右側のテーブルから一致するレコードを返します。
RIGHT (OUTER) JOIN: 右側のテーブルのすべてのレコードと、左側のテーブルから一致するレコードを返します。
FULL (OUTER) JOIN: 左右のどちらかのテーブルで一致するレコードすべてを返します。

SQL エイリアス

SQL エイリアスはテーブルまたはテーブル内の列に一時的な名前を付けるために使用されます。

エイリアスはしばしば列名をより読みやすくするために使用されます。

エイリアスはそのクエリの実行中のみ存在します。

エイリアスは AS キーワードで作成されます。

例

Customers テーブルから CustomerID 列を ID として選択します：

SELECT CustomerID AS ID
FROM Customers;

AS キーワードは実際にはほとんどのデータベース言語で省略することができ、同じ結果を得ることができます：

SELECT CustomerID ID
FROM Customers;

構文

列にエイリアスを使用する場合：

SELECT column_name AS alias_name
FROM table_name;

テーブルにエイリアスを使用する場合：

SELECT column_name(s)
FROM table_name AS alias_name;

デモデータベース

以下は、例に使用される Customers および Orders テーブルからの選択です：

Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	7/4/1996	3
10249	81	6	7/5/1996	1
10250	34	4	7/8/1996	2

列のエイリアス

以下の SQL 文は、CustomerID 列と CustomerName 列のためにそれぞれ ID と Customer のエイリアスを作成します：

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

スペースを含むエイリアスの使用

エイリアスにスペースを含めたい場合、「My Great Products」のように、エイリアスを角かっこまたは二重引用符で囲みます。

角かっこを使用したエイリアスの例：

SELECT ProductName AS [My Great Products]
FROM Products;

二重引用符を使用したエイリアスの例：

SELECT ProductName AS "My Great Products"
FROM Products;

注：一部のデータベースシステムは両方の方法を許可していますが、一部はどちらか一方のみを許可しています。

列の連結

以下の SQL 文は、Address、PostalCode、City、Country の 4 つの列を結合して Address というエイリアスを作成します：

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

注：MySQL で上記の SQL 文を動作させるには、次のようにします：

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

注：Oracle で上記の SQL 文を動作させるには、次のようにします：

SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' || Country) AS Address
FROM Customers;

テーブルのエイリアス

テーブルのえいを使用する場合も同様のルールが適用されます。

例：

SELECT * FROM Customers AS Persons;

テーブルにエイリアスを使用するのは無駄に思えるかもしれませんが、複数のテーブルをクエリで使用する場合、SQL 文を短くすることができます。

以下の SQL 文は、Customers テーブルと Orders テーブルから、テーブルのエイリアスをそれぞれ “c” および “o” として使用しています（SQL を短くするためにエイリアスを使用しています）：

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

以下の SQL 文は、エイリアスを使用せずに同じです：

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

エイリアスは以下のような場面で便利です：

複数のテーブルがクエリに関与する場合
クエリで関数が使用されている場合
列名が長い場合や読みにくい場合
2 つ以上の列が結合されている場合