Wednesday, 16 September 2015

What is the difference between Where clause and having clause in SQL Server

Now the topic is Discussing about where clause and Having clause clause in SQL server. And also discussing about What is the difference between Where clause and having clause in SQL Server.
Where we are using Where clause in SQL Server

                We are using where clause in Update and in select statements. For example, if you have a employee like following image.
Employee-Table-difference-where-having-clause

                In that you have 5 columns and 12 records. Now you want maximum salary employee details in the above table. So for that you can write a query as follow.

Select * from employee where salary= (Select Max (salary) from employee)

So in the above you are using a where clause.

On more query, where you can use Where clause.

Update employee set ename = ‘kvali’ where eid = 3

By the above query you can update employee name from ‘raj’ to ‘kvali’ where eid = 3

So in the above two cases you are using Where clause. On more important is you can’t use having clause for the above two queries.

                We hope, by the above two sql queries you can get a idea where you can use “Where” clause.

Now we are using Having clause in SQL Server:

                We are using having clause for aggregate function output. Now we can write a query to get sum of salary of all departments individually.

Select dno, sum (salary) from employee group by dno

From the query we can get sum of salary of all departments individually, but if we want only department details which have more than 15000 salary. Then we are using “Having clause” clause.

Select dno, sum (salary) from employee group by dno having sum (salary) > 15000

Output for the above sql query is as in the following image

where-Having-Clause-used


Important Note: In the above case we can’t use where Clause in the place of Having clause.

                We hope from above two examples, you can clearly get the idea of difference between where clause and having clause.

Still, If you are not getting difference between where clause and Having clause I will give difference between where clause and Having clause in single sentence format. That is

                “Where clause is used to filter table data and Having clause is used to filter aggregate data”. And one more difference is “Where clause can’t be used in Aggregate functions”

Some more important SQL Server topics:

SQL Query to Replace a character with Some other character in a column

SQL Query To Get Top 2 Highest Salary Employee Details In a SQL Server Table

Topic: What is the difference between “where” clause and “Having clause” clause in SQL server, what is the difference between where and having clause, main difference between having clause  and where clause. difference between where clause and having clause, when you are using “where clause” and when you are using having clause.

0 comments:

Post a Comment

 
Copyright © . Freshers Junction - Posts · Comments
Theme Template by Freshers Junction · Powered by Blogger