StepSetGo tricks gives free rewards free product

SQL HAVING Clause vs WHERE Clause

SQL Order of Execution Flow

Step 1 WHERE Filters raw individual data rows before grouping.

➔

Step 2 GROUP BY Aggregates rows into groups or summaries.

➔

Step 3 HAVING Filters summarized groups using aggregate math functions.

When working with relational databases, filtering data accurately is essential. SQL provides two prominent clauses to handle structural filtering conditions: WHERE and HAVING. While they might seem similar at first glance, they serve entirely different execution roles in data pipelines.

Feature Element	HAVING Clause	WHERE Clause
Evaluation Level	Evaluates operations and checks conditions on a grouped set of rows.	Evaluates conditions on each individual row sequentially.
Aggregate Functions	Can be natively implemented with aggregate functions (e.g., COUNT(), SUM(), AVG()).	Cannot be used alongside standard aggregate processing routines directly.
Execution Priority	Processed and compiled priority wise after the GROUP BY stage.	Processed and compiled priority wise before the GROUP BY stage.

SQL HAVING Syntax Framework

To use filtering with groups properly, structures require placement following specific syntax order paths:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Basic Syntax Example

The following query lists the number of customers residing within each distinct country, excluding any country groups containing 5 or fewer customer listings:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

To organize this resulting dataset cleanly from the highest count sequence downward, incorporate the ORDER BY sorting command clause:

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

Advanced Practical Examples (Northwind Database Layout)

Consider the structural definitions and context samples provided from the schema tables below:

Orders Table

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

Employees Table

EmployeeID	LastName	FirstName	BirthDate	Notes
1	Davolio	Nancy	1968-12-08	Education includes a BA...
2	Fuller	Andrew	1952-02-19	Andrew received his BTS...
3	Leverling	Janet	1963-08-30	Janet has a BS degree...

Example 1: Using HAVING with an INNER JOIN

The query below joins our tables together to count orders handled by unique employee surnames, displaying only the group listings that map to greater than 10 total historical orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

Example 2: Combining WHERE and HAVING Together

This query demonstrates how WHERE and HAVING work seamlessly in the same instruction line. The WHERE filter runs first to isolate entries for specific employees ('Davolio' or 'Fuller'). Then, data rows are grouped and evaluated via HAVING to ensure the resulting dataset strictly includes records surpassing a threshold of 25 cumulative group orders:

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;