Difference Between Where and Having Clause in SQL

If you are a computer science enthusiast, you should be knowing SQL Concepts. WHERE and HAVING are two clauses used in SQL, and it is also one of the most basic concepts asked by any interviewer. WHERE clause is used in filtering data from a table based on certain conditions. While the HAVING clause is used in filtering grouped data. 

What is WHERE Clause in SQL?

The WHERE clause filters records according to the specified criteria. The specified condition is compared with each individual row in the table. Then, as a result of this clause, the rows that satisfy the criteria are returned or altered. 

Any Boolean statement containing numeric or string values and operators like >,<, =, NOT, OR, LIKE, etc., can be used as the condition in the WHERE clause. It may be applied to statements that include SELECT, INSERT, UPDATE, and DELETE. 

WHERE clause is placed before a GROUP BY clause (which is optional) and after FROM clause.  Any aggregate function like AVG(), SUM(), MAX(), MIN(), etc., are not permitted in the WHERE clause.

Syntax of WHERE Clause

SELECT <column_name>
FROM <table_name>
WHERE <search_criteria(s)>

Examples

In this section, we will discuss some examples to get a clear view of where clause.

The tables given here will be used as example tables throughout the article. 

We have two tables here: Customers and Orders.

Customers

Orders

Example 1:

Here in this example, we are selecting the order id and item from the orders table where the amount is greater than or equal to 400. The syntax for this condition is: 

SELECT order_id, item
FROM Orders
WHERE amount >= 400;

Output

In the output, the order_id and item are only the things where the amount is greater than or equal to 400. 

Example 2:

Now let us see another example to get a better understanding of the WHERE clause. 

SELECT first_name, last_name
FROM Customers
WHERE age >= 28;

Output

In the output above, we have the first_name and last_name of the Customers table where the age is greater than or equal to 28.

Also see, SQL EXCEPT

What is HAVING Clause in SQL?

Using a condition, the HAVING clause can also filter records. However, it is frequently used in conjunction with the GROUP BY clause, in which case it does a grouping of rows rather than an individual row-based filter. 

In essence, column operation implements the HAVING clause. It will return the groups that match the condition by comparing the condition with the group's values. When not used in conjunction with GROUP BY, it operates on individual rows similar to the WHERE clause. 

Only SELECT statements may use the HAVING clause. It is placed after the GROUP BY clause. It may include aggregation operations like SUM, AVERAGE, MIN, etc. 

Syntax of HAVING Clause

SELECT column_name1
FROM table_name
GROUP BY column_name2
HAVING column_name3 = criteria;

Examples

Now let's see some examples of the HAVING clause. 

Example 1:

With the help of these examples, you will be able to understand the HAVING clause in a better way. 

SELECT order_id, item
FROM Orders
GROUP BY item
HAVING amount >= 300;

Output

In this example, the output is one order_id per item (group) that has at least one order amount of 300 or more.

Example 2:

Moving forward, let us see another example to get a better understanding of the HAVING clause. 

SELECT first_name, last_name
FROM Customers
GROUP BY last_name
HAVING age >= 28;

Output

In this example, the output is one first_name per last_name that has at least the age of 28 or more. Now let us move to the next section of the blog to know more about the difference between WHERE and HAVING clause.