SQLのwhere句とhaving句の違い、評価順序が原因で出力されるエラーと原因についてまとめています。
SQLのwhereとhavingの違い
where句もhaving句も絞り込み条件を指定するためのSQLです。以下のように単純な条件指定の場合ですと、結果は全く同じになります。以下は、MySQLで実行しています。
- mysql> select * from employees where emp_no=10001;
- +--------+------------+------------+-----------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+------------+-----------+--------+------------+
- | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
- +--------+------------+------------+-----------+--------+------------+
- 1 row in set (0.00 sec)
- mysql> select * from employees having emp_no=10001;
- +--------+------------+------------+-----------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+------------+-----------+--------+------------+
- | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
- +--------+------------+------------+-----------+--------+------------+
- 1 row in set (0.09 sec)
ただし、一般的にwhereよりもhavingのほうがレスポンスが遅くなります。特に理由がなければ、絞り込み条件はwhere句で指定するのが良いでしょう。
【関連記事】
▶SQL having 集約関数の絞り込み whereよりもレスポンスが遅い理由とは？
havingは、集計関数の絞り込み条件指定が可能
havingは、sum、max、avg、countなどの集計関数に対する絞り込み条件を指定することが可能です。
以下は、部署ごとの平均年収のうち、平均年収が7万ドル以上の部署のみを抽出するSQLです。
- SELECT
- departments.dept_name as bushomei,
- avg(salary) as avg_kyuuryo
- FROM
- salaries
- left join dept_emp ON dept_emp.emp_no = salaries.emp_no
- left join departments ON departments.dept_no = dept_emp.dept_no
- WHERE
- salaries.to_date="9999-01-01"
- group by
- bushomei
- having
- avg_kyuuryo > 70000
- +-----------+-------------+
- | bushomei | avg_kyuuryo |
- +-----------+-------------+
- | Sales | 88842.1590 |
- | Marketing | 80014.6861 |
- | Finance | 78644.9069 |
- +-----------+-------------+
- 3 rows in set (3.45 sec)
where句で指定している salaries.to_date=”9999-01-01″ は、最新の年収金額を抽出するための条件です。whereとは別に、平均年収（avg_kyuuryo）が70000以上という条件をhaving句で指定しています。
whereとhavingの評価順序
実は、whereとhavingは評価順序が異なります。以下の順番で評価されるんですね。
- where
- group by
- having
SQLのavg関数は、group byの評価が終わって始めて値が抽出されます。そのため、whereでavg関数の値を条件に指定することはできないんですね。group byの評価が完了したあとのhaving句にはavgの値を指定できます。
実際に、whereとhavingの2箇所で指定した条件を、whereに指定して試してみましょう。havingの条件をwhereに追記すると、以下のエラーが出力されました。
- SELECT
- departments.dept_name as bushomei,
- avg(salary) as avg_kyuuryo
- FROM
- salaries
- left join dept_emp ON dept_emp.emp_no = salaries.emp_no
- left join departments ON departments.dept_no = dept_emp.dept_no
- WHERE
- salaries.to_date="9999-01-01"
- and avg_kyuuryo > 70000
- group by
- bushomei;
- ERROR 1054 (42S22): Unknown column 'avg_kyuuryo' in 'where clause'
havingではselectの列のエイリアス、avg_kyuuryoが使えたのにwhereでは使えないようで、avg_kyuuryoは不明なカラム名だというエラーになりました。
実は一般的なDBMSでは、selectでasを使って別名定義した名前はwhere句やhaving句では使えません。MySQLの拡張で、havingでのみ別名定義が使えるようになっているんですね。
それでは、別名ではなくavg関数をそのまま条件式に指定するとどうなるのでしょう？
and avg_kyuuryo > 70000の箇所をavg(salary) > 70000 に書き換えると、また別のエラーが出力されました。
- ERROR 1111 (HY000): Invalid use of group function
グループ関数（集計関数）の使い方が間違っている、というエラーです。エラーメッセージからは直感的にわかりづらいですが、where句で集計関数は使えないというふうに考えて良いと思います。
まとめ
- where句とhaving句はどちらも絞り込み条件を指定できる
- having句は、avg、max、sum、countなどの集計関数の絞り込み条件が指定可能
- 一般的にhaving句はレスポンスが遅くなるので、集計関数以外の条件はwhereで指定する
