What is difference between Where, Group by and HAVING clause

Following is employee Table where I will demonstrate the Where, Group by and HAVING clause.

EMPNO	ENAME	JOB	SAL	DEPT	DEPTNO	DNAME	LOC
NULL	NULL	NULL	NULL	NULL	1	ACCOUNTING	ST LOUIS
NULL	NULL	NULL	NULL	NULL	2	RESEARCH	NEW YORK
2	Anil	Manager	12000	3	3	SALES	ATLANTA
3	Adam	Developer	6700	3	3	SALES	ATLANTA
5	Tina	Developer	3000	3	3	SALES	ATLANTA
1	Alok	Admin	18000	4	4	OPERATIONS	SEATTLE
4	Anit	Developer	4500	4	4	OPERATIONS	SEATTLE

Where Clause-

WHERE can be used clause with SELECT, INSERT, UPDATE and DELETE statement to filtering rows and it applies on each and every row.

• WHERE clause is used before GROUP BY clause.
• We can't use aggregate functions in the where clause

Fetching all employee that belongs to DEP 3

SELECT * FROM  EMP WHERE EMP.DEPT=3

OutPut:-

EMPNO	ENAME	JOB	SAL	DEPT	DEPTNO	DNAME	LOC
2	Anil	Manager	12000	3	3	SALES	ATLANTA
3	Adam	Developer	6700	3	3	SALES	ATLANTA
5	Tina	Developer	3000	3	3	SALES	ATLANTA

Group by Clause-

Group by is used to group the result set or we can say that gather all the rows together on the based on the one or more columns. It allows aggregate functions to be performed on the one or more columns.

• To use Group by Clause, we need to use at least one aggregate function
• We can use Group by Clause with or without Where Clause.

-- Counting employee in each department by using GROUP BY clause

SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM GROUP BY DNAME

OutPut:-

DNAME	TotalEmployee
ACCOUNTING	0
OPERATIONS	2
RESEARCH	0
SALES	3

HAVING Clause-

HAVING is used to trying to filter based on one of the grouping fields. It can be used in a SELECT statement to filter the records that a GROUP BY.

• HAVING clause is used to filter grouping fields.
• HAVING clause is used after GROUP BY clause.

Fetching department that have at least one employee by using HAVING clause example

SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM  EMP GROUP BY DNAME HAVING COUNT(EMPNO)>1

OutPut:-

DEPTNO	DNAME	TotalEmployee
4	OPERATIONS	2
3	SALES	3

 Fetching department that have no employee by using HAVING clause example

SELECT DNAME, COUNT(EMPNO) AS TotalEmployee FROM  EMP GROUP BY DNAME HAVING COUNT(EMPNO)<1

OutPut:-

DEPTNO	DNAME	TotalEmployee
1	ACCOUNTING	0
2	RESEARCH	0

I am hoping that you are enjoying with this post! Please share with you friends. Thank you!!