What is the difference between having and where in SQL?

by | Aug 12, 2024 | SQL | 0 comments

390 Views

What is the WHERE Clause?

The WHERE clause in SQL is used to filter records from a table based on specified conditions before any grouping is applied. It acts like a filter to select only those rows that meet the given criteria. This clause is essential for narrowing down data for analysis or manipulation. The WHERE clause can incorporate logical operators such as AND, OR, and NOT to refine the selection of data.

Operations Supported:

  • SELECT
  • UPDATE
  • DELETE

Example

Consider the following table BOOKS:

BOOK_ID BOOK_NAME PRICE
89 A 230
90 B 430
91 C 324
92 D 730

SQL Query:

sql

SELECT BOOK_ID, PRICE FROM BOOKS WHERE PRICE > 350;

Output:

BOOK_ID PRICE
90 430
92 730

This query filters and retrieves only those books with a price greater than 350.

What is the HAVING Clause?

The HAVING clause is used to filter records from groups based on specified conditions. It is often used in conjunction with aggregate functions to filter group-level data after the grouping operation is performed. Unlike the WHERE clause, which operates on rows, the HAVING clause operates on groups of rows and is applicable only with SELECT statements.

Applicable Aggregate Functions:

  • COUNT()
  • SUM()
  • AVG()
  • MAX()
  • MIN()

Example

Consider the following table BOOKS:

BOOK_ID BOOK_NAME LANGUAGE PRICE
89 A Hindi 22000
90 B Hindi 20000
91 C English 25000

If we want to count the number of books in each language where the count of books per language is greater than 1, we can use the HAVING clause.

SQL Query:

sql

SELECT COUNT(BOOK_ID), LANGUAGE FROM BOOKS GROUP BY LANGUAGE HAVING COUNT(LANGUAGE) > 1;

Output:

COUNT(BOOK_ID) LANGUAGE
2 Hindi

This query retrieves languages with more than one book, showing the power of HAVING in filtering group results.

Difference Between WHERE and HAVING Clauses in SQL

Aspect WHERE Clause HAVING Clause
Purpose Filters rows before grouping Filters groups after grouping
Usage Can be used without GROUP BY Must be used with GROUP BY
Applicable Functions Row functions Column functions
Operations Supported SELECT, UPDATE, DELETE SELECT only
Order of Execution Applied before GROUP BY Applied after GROUP BY
Example Functions Single-row operations (e.g., UPPER, LOWER) Aggregate operations (e.g., SUM, COUNT)

In summary, both WHERE and HAVING clauses are vital in SQL for data filtering. The WHERE clause operates at the row level, ideal for initial data filtering, while the HAVING clause is used for post-group filtering, specifically focusing on groups of data and aggregate results. Understanding the distinct roles and functionalities of these clauses can significantly enhance the effectiveness of data querying and manipulation in SQL.