zlika's

The difference between WHERE and HAVING filters in Transact-SQL queries is that WHERE clause is a filter of all the data, while HAVING filters the GROUP BY data. I’ve prepared a simple example.

USE zlika
GO

-- Create table
CREATE TABLE test (
	Date DATETIME
	, QTY INT
)
GO

-- Populate table
INSERT INTO test (Date, QTY)
SELECT '2009-05-10', 1
UNION ALL SELECT '2009-05-10', 2
UNION ALL SELECT '2009-05-10', 3
UNION ALL SELECT '2009-05-11', 4
UNION ALL SELECT '2009-05-11', 5
UNION ALL SELECT '2009-05-11', 6
UNION ALL SELECT '2009-05-12', 7
UNION ALL SELECT '2009-05-12', 8
UNION ALL SELECT '2009-05-12', 9
GO

-- Check the table
SELECT
	CONVERT(CHAR(10), Date, 103) AS Date
	, QTY

FROM
	test
GO

The content of the table is as follows:

Content of the table

If we GROUP BY column QTY, the result is different sums of the QTY column:
May 10, 2009, 1 + 2 + 3 = 6
May 11, 2009, 4 + 5 + 6 = 15
May 12, 2009, 7 + 8 + 9 = 24

USE zlika
GO

-- GROUP BY (no filters)
SELECT
	CONVERT(CHAR(10), Date, 103) AS Date
	, SUM(QTY) AS QTY

FROM
	test

GROUP BY
	Date
GO

GROPU BY filter

If we exclude the ’6′ in the WHERE clause, it’s gonna be filtered before the grouping of the date. The line No. 6 will be eliminated and the May 11, 2009 will be grouped like 4 + 5 = 9.

USE zlika
GO

-- GROUP BY (WHERE filter)
SELECT
	CONVERT(CHAR(10), Date, 103) AS Date
	, SUM(QTY) AS QTY

FROM
	test

WHERE
	QTY <> 6

GROUP BY
	Date
GO

WHERE filter

If we exclude the ’6′ in the HAVING clause, it’s gonna be filtered after the grouping of the date. The date that has sum of QTY column = 6 will be eliminated and the date May 10, 2009 will miss.

USE zlika
GO

-- GROUP BY (HAVING filter)
SELECT
	CONVERT(CHAR(10), Date, 103) AS Date
	, SUM(QTY) AS QTY

FROM
	test

GROUP BY
	Date

HAVING
	SUM(QTY) <> 6
GO

HAVING filter

Clean up the test table.

USE zlika
GO

-- Clean up
DROP TABLE test
GO

Félicitations