Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQL havingについて、havingの構文やwhereとの違い、各DBMSでの独自拡張や注意点をまとめています。

SQL havngの構文

SQLはcount()やsumなどの集約・集計関数に対して条件指定を行います。

以下は、departments（部署）テーブルの社員数が30,000人以上のレコードを抽出するSQLです。社員数をカウントするために、dept_emp（部署・社員紐付けテーブル）のdept_no（部署名）でgroup by指定し、emp_no（社員番号）の数をカウントしています。

SELECT dept_name, count(emp_no) FROM `dept_emp`
left join departments on dept_emp.dept_no = departments.dept_no
group by dept_emp.dept_no
having count(emp_no) > 30000

実行するとこうなります。30,000人以上の社員のいる部署のみが抽出されています。

+-------------+---------------+
| dept_name   | count(emp_no) |
+-------------+---------------+
| Production  |         73485 |
| Development |         85707 |
| Sales       |         52245 |
+-------------+---------------+
3 rows in set (1.37 sec)

【関連記事】
▶SQL having 集約関数の絞り込み whereよりもレスポンスが遅い理由とは？ 

WHEREとHAVINGの違い

一見すると、条件指定ならwhereで良いのでは？と考えてしまいますよね。実際にどうなるのか、whereで条件を指定してみましょう。

SELECT dept_name, count(emp_no) FROM `dept_emp`
left join departments on dept_emp.dept_no = departments.dept_no
where count(emp_no) > 30000
group by dept_emp.dept_no

MySQLで実行するとこうなります。

ERROR 1111 (HY000): Invalid use of group function

group function（集約関数）の使い方が間違っている、というエラーメッセージが出力されました。

クエリの実行順序的に、whereに集約関数を指定することは出来ないんですね。

内部処理としてhaving指定は、selectのwhere指定でレコードを抽出し、group by指定に従ってグルーピング集計、その後にhavingの条件を適用することになります。

なお、単純にwhereの代わりにhavingを使うことも可能です。

mysql> select * from employees
    -> having emp_no > 200000
    -> limit 10;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
| 200001 | 1957-09-10 | Bedrich    | Markovitch  | M      | 1985-11-22 |
| 200002 | 1961-02-07 | Pascal     | Benzmuller  | F      | 1986-03-12 |
| 200003 | 1958-10-14 | Arvind     | Dechter     | M      | 1987-09-29 |
| 200004 | 1960-09-03 | Masaru     | Rusmann     | M      | 1985-02-18 |
| 200005 | 1961-12-24 | Geoffry    | Sherertz    | M      | 1986-07-22 |
| 200006 | 1960-11-25 | Jacopo     | Marchesini  | M      | 1985-05-15 |
| 200007 | 1957-09-11 | Danae      | Rouquie     | F      | 1998-04-21 |
| 200008 | 1953-08-05 | Zeydy      | Hutter      | F      | 1986-10-10 |
| 200009 | 1954-03-17 | Gererd     | Capobianchi | F      | 1989-07-12 |
| 200010 | 1954-06-20 | Petter     | Pelz        | F      | 1994-11-14 |
+--------+------------+------------+-------------+--------+------------+
10 rows in set (0.54 sec)

しかし、素直にwhereを使ったほうがレスポンス時間が断然速いんですね。以下は、whereで条件を指定したときのレスポンスです。1/5ほどの時間で結果が返ってきました。

10 rows in set (0.11 sec)

各種DBMSのhaving句

Oracleのhaving句

havingで指定できるのは、同じ文で定義された属性のみです。（SUMの別名定義など）以下の例では、上の文はエラーになり、下の文は正常に処理されます。

// Invalid because Price is not defined in the statement (i.e., Price is a corpus attribute).
Return results AS
SELECT SUM(Price) AS TotalPrices
GROUP BY WineType
HAVING Price > 100

// Valid because TotalPrices is defined in the statement.
Return results AS
SELECT SUM(Price) AS TotalPrices
GROUP BY WineType
HAVING TotalPrices > 100

参考）Oracle公式ドキュメント：HAVING句

SQL Serverのhaving句

text、image、および ntext 型は HAVING 句では使用できません。

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

参考）HAVING (Transact-SQL) – SQL Server | Microsoft Docs

MySQLのhaving句

MySQLでは、group byで指定していないカラム（以下の例だとc.nameの部分）を対象にselectが可能です。これはMySQLの独自拡張で、標準SQLではエラーとなります。

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.3 MySQL での GROUP BY の処理

なお、上記の拡張はMySQL5.7以降ではデフォルトで無効（ONLY_FULL_GROUP_BYがオン）になっています。

ONLY_FULL_GROUP_BYの状態を確認するには、以下のSQLを実行します。

mysql> SELECT @@global.sql_mode;
+---------------------------------------------------------------------------- ------------------------------------------+
| @@global.sql_mode                                                                                                     |
+---------------------------------------------------------------------------- ------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_F R_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------- ------------------------------------------+
1 row in set (0.00 sec)

@@global.sql_modeに「ONLY_FULL_GROUP_BY」が入っていますね。

上記の拡張は、ONLY_FULL_GROUP_BYをオフにすると有効になります。

ONLY_FULL_GRUP_BYをオフにするには、以下のSQLを実行します。

SET SESION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

※上記指定は「SESSION」と指定することで、現在接続中のセッションのみに反映させます。「GLOBAL」と指定して、再起動するとデーターベース全体で有効になります。

PostgreSQLのhaving句

PostgreSQLでは、特にhaving句での独自拡張はありません。

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;
  city   | max
---------+-----
 Hayward |  37
(1 row)

参考）PostgreSQLの公式ドキュメント：集約関数

まとめ

ポテパンダの一言メモ
  • having句は、集約関数に対して条件を指定可能
  • whereの代わりにhavingを指定できるが実行速度が遅い
  • MySQLのhavingは、ONLY_FULL_GROUP_BYをオフにするとgroup by指定していないカラムをselectできる

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。