WHERE vs HAVING and GROUP BY vs PARTITION BY Clause in SQL

In SQL the WHERE and HAVING clause is the most common keyword. The purpose for both is somewhat similar. They are very confusing if you consider the different databases like MS SQL, MySQL, and Oracle. That is the reason, they are most common in the interview. In this article, we will walk through the most common difference between them along with the GROUP BY vs PARTITION BY clause.

Please visit our YouTube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Today's Interview Questions

Q01. What is the difference between the WHERE vs HAVING Clause?

Q02. What is the difference between the GROUP BY vs PARTITION BY Clause?

Q03. Can you use both WHERE and HAVING clauses in any SQL statement? What will be the order of both?

Q04. Can you use Aggregate Functions in the WHERE clause?

Q05. Can you use the HAVING clause without the GROUP BY clause?

Q06. Can you use the HAVING clause without any Aggregate Functions?

Q07. How the GROUP BY clause is different from the PARTITION BY clause?

Q08. Can you use the HAVING clause to UPDATE or DELETE the records?

Q09. Can you use Aggregate Functions without the GROUP BY clause?

Q10. Can you use the HAVING clause in JOIN?

Introduction

The WHERE vs HAVING clause is one of the important SQL concepts to understand, not just from an interview perspective but also from a day-to-day use perspective as well.

They have some similarities and some differences as well. We will see both one by one here in this article. We will use the below Employee table for the demo.

$D:\TechPointFundamental\YouTube\SQL\SQL Clauses\HAVING-vs-WHERE-Clause\Snapshots$

WHERE Clause and HAVING Clause in SQL

Though both are used to exclude (filter) rows from the result set, we use the WHERE clause to filter rows before grouping and use the HAVING clause to filter rows after grouping. So the WHERE condition is evaluated per row before the grouping of records. On the other hand, the HAVING clause is evaluated per group.

The WHERE clause is used to filter the records based on the table columns while the HAVING clause is generally used to filter the rows based on aggregate functions like count, sum, avg, min, and max, etc.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

In SQL the WHERE clause is processed right after FROM clause in the logical order of query processing, which means it is processed before the GROUP BY clause while the HAVING clause is executed after groups are created.

The HAVING clause is often used with the GROUP BY clause in the SELECT statement. If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like the WHERE clause (In some databases).

However, a query can contain both a WHERE clause and a HAVING clause, and a HAVING clause also contains a GROUP BY clause. If you use both WHERE and HAVING clauses in a query then:

The WHERE clause is applied first to the individual rows in the result set of query and it returns only the matching rows with the WHERE filter criteria.
After that, the groups are created that meet the conditions in the WHERE clause.
After that, the HAVING clause is applied to filter the groups from the result set. Here only the groups that meet the HAVING conditions appear in the final query output.
At last, the ORDER BY clause will be executed if there is any.

You can create a filter or search condition criteria either in the WHERE clause or the HAVING clause or you can also divide it in both the WHERE and HAVING clauses combinedly. So you can create a WHERE clause and HAVING clause involving the same column.

HAVING Clause vs GRPUP BY Clause

In MS SQL generally, the GROUP BY clause is mandatory if you use the HAVING clause, but you can use the HAVING clause without the GROUP BY class as well in some cases.

You can write the SQL query without GROUP BY but you can only use the aggregate functions in the select list of the query.

However, you can use all the aggregate functions in the SELECT list along with the other columns of the table by using the PARTITION BY clause.

In SQL the GROUP BY clause is used to summarize or aggregate the rows on which any aggregate function can be applied. After Grouping the data, you can filter the grouped record using the HAVING clause.

The GROUP BY clause returns only one result per group of records. GROUP BY Clause always follows the WHERE Clause if any.

The GROUP BY Clause always precedes the ORDER BY clause if any.

DISTINCT Clause vs GRPUP BY Clause

In SQL the DISTINCT clause returns the unique values present in the column. If it is used with multiple select list columns then it returns all the unique combinations of the column's values.

On the other hand, the GROUP BY clause returns the unique items with the aggregate resultant column only.

However, you can use both the DISTINCT and GROUP BY clauses together in the SQL Query.

GROUP BY Clause vs PARTITION BY Clause

In SQL the PARTITION BY clause is used to divide the result set into partitions and perform computation on each subset of partitioned data. It is used to partition rows of tables into groups. It is useful when we have to perform a calculation on individual rows of a group using other rows of that group.

The PARTITION BY clause is always used inside the OVER() clause. The partition formed by the PARTITION BY clause is also known as Window.

It is also useful if we want to display other columns of the table along with aggregate functions apart from the GROUP BY columns.

Similarities Between WHERE Clause and HAVING Clause

The WHERE and HAVING clauses have some similarities. Let's see some of them:

Both can be used with the SELECT clause.
Both are optional clauses with the SELECT statement.
Both can be used to filter the records.
Both precede the ORDER BY clause.
Both can be used with the GROUP BY Clause.
Both can be used with the PARTITION BY Clause.
Both can be used as isolated or together to achieve the desired result.
Both can be used to UPDATE and DELETE the records in some databases like Oracle.

A HAVING clause is the same as a WHERE clause, except the HAVING clause, applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Difference Between WHERE Clause and HAVING Clause

In SQL HAVING Clause is used as a conditional clause with GROUP BY Clause. The HAVING clause is there in SQL because the WHERE clause cannot be used with aggregate results, so it has a different purpose. The primary purpose of the WHERE clause is to deal with non-aggregated or individual records.

A WHERE clause is used to filter the records from the table based on the specified condition while the HAVING clause is used to filter records from the groups based on the specified condition.
A WHERE clause can be used with SELECT, UPDATE, DELETE, INSERT statement, but HAVING Clause can only be used with SELECT statement in MS SQL.
A WHERE clause cannot contain an aggregate function but a HAVING clause can contain an aggregate function. i.e.
WHERE Clause is used only with single row function like UPPER, LOWER while HAVING Clause is used with multiple row function like SUM, COUNT.
A WHERE clause can be used without GROUP BY Clause but generally HAVING Clause requires GROUP BY Clause.
WHERE Clause is used before GROUP BY Clause, HAVING Clause is used after GROUP BY Clause.
WHERE Clause implements in row operations while HAVING Clause implements in column operation.
You can apply a HAVING clause only to columns that either appear in the GROUP BY clause or in an aggregate function. On the other hand, this is not mandatory for the WHERE clause.

Live Demo

Conclusion

Normally WHERE clause is used to filter the individual rows, while the HAVING clause is used to filter the individual group.

The WHERE clause can be used with all the DML commands but HAVING can only be used with the SELECT command only.

A WHERE clause cannot use any aggregate function but the HAVING clause can use the aggregate functions.

The WHERE clause is always used before the GROUP BY and HAVING clause.

SQL Scripts

   
 

 -- Create Table
 CREATE TABLE Employee
 (
		  ID INT IDENTITY PRIMARY KEY,
		  Name VARCHAR(50) NULL,
		  RoleName VARCHAR(10) NULL,
		  Salary NUMERIC(18,2) NULL
);

-- Insert sample data into table
INSERT INTO Employee
VALUES ( 'Alex', 'SuperAdmin', 90000 ),
		( 'Thomas', 'Admin', 80000 ),
		( 'Peter', 'SuperAdmin', 95000 ),
		( 'Sid', 'SuperAdmin', 70000 ),
		( 'Maria', 'Admin', 60000 ),
		( 'Andrew', 'Manager', 50000 ),
		( 'Fedric', 'SuperAdmin', 45000 ),
		( 'Anton', 'Supervisor', 93000 ),
		( 'Mandy', 'Manager', 30000 );
		
		
-- Employee Table Data 
SELECT * FROM Employee;


-- WHERE vs HAVING Clause
SELECT RoleName, Name FROM Employee 
WHERE RoleName = 'SuperAdmin';

SELECT RoleName, Name FROM Employee 
GROUP BY Name, RoleName
HAVING RoleName = 'SuperAdmin';


-- HAVING Clause with WHERE Clause
SELECT RoleName, Salary FROM Employee 
WHERE  Salary > 45000 
GROUP BY RoleName, Salary
HAVING RoleName = 'SuperAdmin';

-- Interchanging Filter Criteria in WHERE vs HAVING Clause
SELECT 	RoleName, Salary FROM Employee 
WHERE	RoleName = 'SuperAdmin' 
GROUP BY RoleName, Salary
HAVING Salary > 45000;

-- Using Aggregate Functions without HAVING Clause
SELECT			
		AVG(Salary) AS AvgSalary,
		SUM(Salary) AS TotalSalary,
		Min(Salary) AS MinSalary,
		MAX(Salary) AS MaxSalary,
		COUNT(*) AS TotalCount
FROM Employee	
WHERE RoleName = 'SuperAdmin';


-- HAVING Clause without GROUP BY Clause
SELECT			
		AVG(Salary) AS AvgSalary,
		SUM(Salary) AS TotalSalary,
		Min(Salary) AS MinSalary,
		MAX(Salary) AS MaxSalary,
		COUNT(*) AS TotalCount
FROM Employee
HAVING COUNT(*) > 1;

-- PARTITION BY Clause for Aggregate Function with WHERE Clause
SELECT	
	Id,			
	RoleName,
	Name AS EmployeeName,	
	Salary,				
	MIN(Salary) OVER(PARTITION BY RoleName) AS MinSalary,	
	MAX(Salary) OVER(PARTITION BY RoleName)  AS MaxSalary,
	SUM(Salary) OVER(PARTITION BY RoleName)  AS TotalSalary,
	COUNT(ID)	OVER(PARTITION BY RoleName)  AS TotalCount
FROM Employee
WHERE RoleName != 'Supervisor'
ORDER BY RoleName;

Sunday, March 20, 2022

WHERE vs HAVING and GROUP BY vs PARTITION BY Clause in SQL