Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Jul 10, 2024
Easy

Difference Between Where and Having Clause in SQL

Author Shivani Singh
0 upvote
Table of contents
Learn to use AI Tools & ChatGPT to excel as Microsoft SDE
11 Jul, 2024 @ 01:30 PM
Speaker
Pranav Malik
SDE 2 @

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. 

Difference between WHERE and HAVING clause

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

customer_idfirst_namelast_nameagecountry
1JohnDoe31USA
2AlphaLuna22USA
3DavidRobinson22UK
4RobertPattinson25UK
5BettyDoe28UAE

Orders

order_iditemamountcustomer_id
1Keyboard4004
2Mouse3004
3Monitor120003
4Joystick4001
5Scanner2502

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

order_idItems
1Keyboard
3Monitor
4Keyboard

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

first_namelast_name
JohnDoe
BettyDoe

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

order_idItems
1Keyboard
3Monitor
2Mouse

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

first_namelast_name
JohnDoe

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. 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Difference Between Where and Having Clause in SQL

BasisWHERE ClauseHAVING Clause
UseThe table's rows can be filtered using the WHERE clause.Records from the groups are filtered using the HAVING clause.
PositionIt is used before GROUP BY in the SQL statement.It is used after the GROUP BY.
Filtering ConditionsThe WHERE clause applies filtering conditions to individual rows.It applies conditions to the aggregate results.
Aggregate FunctionsConditions involving aggregate functions like min(), max(), etc are incompatible with the WHERE clause.A HAVING clause may serve aggregate functions like min(), max(), and sum().
SpeedA WHERE clause is faster than the HAVING clause.The HAVING clause is slower than the WHERE clause.

Also see, Tcl Commands in SQL

Key Difference Between Where and Having Clause in SQL

  • WHERE clause filters individual rows before grouping, while HAVING clause filters aggregated results after GROUP BY.
  • WHERE is used with row-level conditions, while HAVING is used with aggregate functions.
  • WHERE comes before GROUP BY, HAVING comes after.
  • WHERE affects which rows are in the result set, HAVING affects which groups are in the result set.
  • WHERE is for filtering individual data points, HAVING is for filtering groups of data.

Frequently Asked Questions

Which clause is faster WHERE or HAVING?

The WHERE clause is typically faster than the HAVING clause. WHERE filters rows before grouping, while HAVING filters grouped results, so WHERE reduces the dataset earlier, often resulting in better performance.

What is the advantage of HAVING clause over WHERE?

The HAVING clause is used after GROUP BY and allows filtering aggregated results based on conditions. It's advantageous for filtering grouped data, while WHERE filters individual rows before grouping.

Where can we use the HAVING clause?

The HAVING clause is used in SQL queries with the GROUP BY clause. It filters grouped results based on conditions, allowing you to apply conditions to aggregated data.

Can I use WHERE instead of HAVING?

You can use WHERE to filter rows before grouping, but HAVING is specifically for filtering aggregated results after using GROUP BY, so they serve different purposes.

Conclusion

As we have reached the end of this blog, let us see what we have discussed so far. In this blog, we have seen the basics of the WHERE clause. We saw its syntax and some examples. Then we discussed the HAVING clause along with its syntax and examples. In the end, we saw the difference between WHERE and HAVING clause.  

Read More Articles

For more content, Refer to our guided paths on Code360 to upskill yourself in Data Structures and AlgorithmsCompetitive ProgrammingJavaScriptSystem Design, and many more! 

If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Code360! But if you have just started your learning process and looking for questions asked by tech giants like Amazon, Microsoft, Uber, etc., you must have a look at the problems, interview experiences, and interview bundle for placement preparations.

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!

Previous article
Difference between DDL and DML in SQL
Next article
AND & OR Clause