How do you use the HAVING clause in SQL, and how does it differ from the WHERE clause? — SQL & Databases Interview Question #11

The HAVING clause in SQL is used to filter the results of a query based on the result of an aggregate function. The HAVING clause is often used in conjunction with the GROUP BY clause, and it allows you to filter groups of rows based on a specific criterion.

Here is an example of a SQL statement that uses the HAVING clause:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

In this example, the HAVING clause is used to filter the result set based on the average salary of each department. The GROUP BY clause groups employees by department, and the AVG function calculates the average salary for each group. The HAVING clause filters out any groups whose average salary is less than or equal to 50000.

The HAVING clause differs from the WHERE clause in that the WHERE clause is used to filter rows based on a specific condition, whereas the HAVING clause is used to filter groups of rows based on an aggregate function. The WHERE clause filters rows before they are grouped, while the HAVING clause filters groups after they are grouped.

Here is an example of a SQL statement that uses the WHERE clause:

SELECT * FROM employees WHERE salary > 50000;

In this example, the WHERE clause is used to filter out any employees whose salary is less than or equal to 50000. This filtering is done before any grouping or aggregation is performed.

In summary, the HAVING clause is used to filter the results of a query based on the result of an aggregate function, and it is used in conjunction with the GROUP BY clause. The WHERE clause is used to filter rows based on a specific condition, and it is used independently of the GROUP BY clause. By using both the HAVING and WHERE clauses, you can filter and manipulate data in various ways to derive insights and draw conclusions from your data set.