Filtering with “WHERE and HAVING” Command in SQL

For SQL users we all agree that the SELECT, FROM, and WHERE commands are the most used commands in SQL. For sure these are the first 3 commands everyone starts with. However, the HAVING command is also such a great command that can be very helpful when filtering.

The WHERE and HAVING commands are powerful tools for filtering data in SQL. It is important to understand How and When to use them. This article centers usage of the WHERE and HAVING commands for effective filtering results.

The WHERE command

The WHERE command is used to filter data based on specific criteria. When using the SELECT statement, the WHERE command is placed after the FROM clause and before the ORDER BY clause (if present). The WHERE command is used to extract only those rows that meet the specified conditions. For example, the following SQL statement will extract all rows from the employees table where the salary is greater than $50,000:

SELECT *
FROM employees
WHERE salary > 50000

In this example, the WHERE clause specifies the condition that only rows with a salary greater than 50,000 should be returned. All other rows will be excluded from the result set.

The HAVING command

The HAVING command, on the other hand, is used to filter data based on aggregate functions such as SUM, AVG, COUNT, MIN, and MAX. The HAVING clause is used in conjunction with the GROUP BY clause to extract only those groups that meet the specified conditions. For example, the following SQL statement will extract all groups from the employees table where the average salary is greater than $50,000:

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

In this example, the GROUP BY clause is used to group rows by department, and the AVG function is used to calculate the average salary for each group. The HAVING clause is used to extract only those groups where the average salary is greater than 50,000. All other groups will be excluded from the result set.

So, the main difference between the WHERE and HAVING clauses is that the WHERE clause filters rows based on individual column values, while the HAVING clause filters groups based on aggregate function values.

When to use WHERE and HAVING?

The WHERE clause is used when we want to filter rows based on a specific condition, such as selecting employees with salaries greater than a certain amount or selecting customers from a specific region.

The HAVING clause, on the other hand, is used when we want to filter groups based on aggregate functions, such as selecting departments with average salaries greater than a certain amount or selecting sales regions with total sales greater than a certain amount.

How to use WHERE and HAVING?

To use the WHERE clause, you must specify the condition that you want to filter on, such as salary > 50000. The WHERE clause can also use logical operators such as AND, OR, and NOT to combine multiple conditions.

For example, the following SQL statement will extract all rows from the employees table where the salary is greater than $50,000 and the department is ‘Sales’

SELECT *
FROM employees
WHERE salary > 50000 AND department = 'Sales';

To use the HAVING clause, you must first group the data using the GROUP BY clause. You can then specify the condition that you want to filter on using the HAVING clause. For example, the following SQL statement will extract all departments from the employees table where the average salary is greater than $50,000:

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

Using WHERE AND HAVING Together

One might want to ask in possible to use both WHERE and HAVING together. The answer is Yes. Oh Yes.

Using the previous example, let’s say using you want to filter the rows from the employees table where the department is ‘Sales:

SELECT * 
FROM employees
WHERE department = 'Sales'
HAVING salary > 50000;

This query first filters all rows from the employees table where the department is ‘Sales’. It then applies the HAVING command to further filter the results and only include rows where the salary is greater than $50,000.

Another example, suppose we have a table called “orders” with the following data:

id product quantity price     date
1   A       10       5.00     2022-01-01
2   A       20       4.50     2022-02-01
3   B       15       6.00     2022-01-01
4   B       25       5.50     2022-02-01
5   C       5        7.00     2022-01-01
6   C       10       6.50     2022-02-01

If we want to extract only the rows where the product is ‘A’ and the total price (quantity * price) is greater than $100, we can use both the WHERE and HAVING clauses as follows:

SELECT product, SUM(quantity * price) as total_price 
FROM orders 
WHERE product = 'A' 
GROUP BY product 
HAVING SUM(quantity * price) > 100;

This will give us the result of product: total_price A 105

First, we used the WHERE clause to filter the rows where the product is ‘A’. Then, we used the GROUP BY clause to group the rows by product and calculated the total price (quantity * price) for each product. Finally, we used the HAVING clause to filter the groups where the total price is greater than $100.

Conclusion

In summary, the WHERE and HAVING clauses are powerful tools in SQL for filtering and aggregating data. The WHERE clause is used to filter rows based on a condition, while the HAVING clause is used to filter groups based on a condition. It’s important to use these clauses appropriately based on the specific requirements of the query. By understanding the difference and appropriate usage of WHERE and HAVING clauses, data analysts can write effective SQL queries that extract meaningful insights from the data.

Thanks for Reading 👌👌