SQL HAVING
Clause
Having clause is used to filter data based on the
group functions. This is similar to WHERE condition but is used with group
functions. Group functions cannot be used in WHERE Clause but can be used in
HAVING clause.
First Create a Table called [dbo].[Employee_Test]
CREATE TABLE [dbo].[Employee_Test](
[Emp_ID] [int] NOT NULL,
[Emp_name] [varchar](100)NOT NULL,
[Emp_Sal] [int] NULL,
[Age] [int] NULL,
[Gender] [nchar](10)NOT NULL
[DeptId] [int] NOT NULL
) ON [PRIMARY]
Now Insert some data in [dbo].[Employee_Test]
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (1, N'Anees', 1000, 30, N'Male', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (2, N'Rinku', 1200, 45, N'Male', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (3, N'John', 1100, 25, N'Male', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (4, N'Stephen', 1300, 30, N'Male', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (5, N'Maria', 1400, 45, N'Female', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (6, N'Ankit', 1000, 25, N'Male', 101)
INSERT INTO [dbo].[Employee_Test] ([Emp_ID], [Emp_name], [Emp_Sal], [Age], [Gender],[DeptId])
VALUES (7, N'Renu', 1200, 30, N'Female', 101)
Now
create a department table
CREATE TABLE [dbo].[tblDept](
[DeptId] [int] NULL,
[DeptName] [varchar](50) NULL,
[DeptLocation] [varchar](50) NULL
)
And
Insert some data into Dept table
USE [Employee]
GO
INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])
VALUES (101, N'Development', N'Bangluru')
GO
INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])
VALUES (102, N'Finance', N'Delhi')
GO
INSERT [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])
VALUES (103, N'HR', N'Hyderabad')
GO
INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])
VALUES (104, N'Sales', N'Delhi')
GO
INSERT INTO [dbo].[tblDept] ([DeptId], [DeptName], [DeptLocation])
VALUES (105, N'Training', N'Bangluru')
GO
Now Data
in [dbo].[Employee_Test]
Now Data
in [dbo].[tblDept]
Records
Without Having Clause
select dp.DeptName, Max([Emp_Sal]) from [dbo].[Employee_Test] emp
inner join [dbo].[tblDept] dp on emp.DeptId=dp.DeptId group by dp.DeptName
Records
With Having Clause
select dp.DeptName, Max([Emp_Sal]) from [dbo].[Employee_Test] emp
inner join [dbo].[tblDept] dp on emp.DeptId=dp.DeptId group by dp.DeptName
having Max([Emp_Sal])>1000
Note:-
When
WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the
WHERE clause is processed first, then the rows that are returned after the
WHERE clause is executed are grouped based on the GROUP BY clause.
Finally,
any conditions on the group functions in the HAVING clause are applied to the
grouped rows before the final output is displayed.
No comments:
Post a Comment