WHERE and HAVING

The `WHERE` and `HAVING` clauses in SQL are both used to filter and restrict the rows returned in a query, but they are used in different contexts.

WHERE Clause

1. Used with SELECT, UPDATE, DELETE

   - The `WHERE` clause is primarily used with the `SELECT`, `UPDATE`, and `DELETE` statements.

2. Filters Rows

   - It is used to filter rows from the result set based on a specified condition.

   - The condition in the `WHERE` clause is applied to individual rows before the aggregation.

3. Applied before GROUP BY

   - When used with aggregation functions (e.g., SUM, AVG) in a SELECT statement, the `WHERE` clause filters rows before they are aggregated.

4. Example

   SELECT column1, column2

   FROM your_table

   WHERE condition;

HAVING Clause

1. Used with GROUP BY

   - The `HAVING` clause is used in conjunction with the `GROUP BY` clause.

2. Filters Groups

   - It is used to filter the results of aggregate functions based on a specified condition.

   - The condition in the `HAVING` clause is applied to groups of rows after they have been aggregated.

3. Applied after GROUP BY

   - The `HAVING` clause is applied after the `GROUP BY` clause and the aggregation functions.

4. Example

 

   SELECT column1, COUNT(*)

   FROM your_table

   GROUP BY column1

   HAVING COUNT(*) > 1;

Summary

- Use the `WHERE` clause to filter individual rows before they are grouped or aggregated.

- Use the `HAVING` clause to filter the results of aggregate functions after they have been grouped.

- If there is no `GROUP BY` clause in your query, you will typically use the `WHERE` clause.

- If you are using aggregate functions with a `GROUP BY` clause, conditions on the aggregated values go in the `HAVING` clause.

In essence, the key distinction is that the `WHERE` clause is used to filter rows before any grouping or aggregation, while the `HAVING` clause is used to filter the results after grouping has occurred.