Welcome to solsarin site ,Keep reading and find the answer about “difference between statement and clause in sql”.
Stay with us.
Thank you for your support.
In this tutorial, I am going to explain the concept of HAVING and WHERE Clause in SQL Server. This detailed article will cover the following topics as follows,
- SQL Order of Execution
- HAVING Clause
- WHERE Clause
- Difference Between HAVING And WHERE Clauses
What are the clause, Keywords and statement in SQL?
Here, we will learn: what are the clauses, keywords and statements in SQL, in a query there are many words used and we have to know which is clause, keyword or statement? So let’s understand in this article.
Throughout the learning of SQL (structured query language), the following terminology (words) will be used:
Consider this Query – based on this Query we will understand these words:
SELECT std_id, std_name, std_age,... FROM student;
The individual tokens (elements) which are predefined, and we cannot change its meaning are known as SQL keyboard.
In the given example: The keyword are “SELECT” and “FROM”
Clause is a part of an SQL statement.
In the given example: The clause is “SELECT std_id, std_name, std_age,…”
A complete query may refer as “statement” and we can say that set of two or more clause are known as a “statement” in SQL.
- leila and eber lambert divorce
- when was the great wall of china built?
- what percentage of fresh water is in the great lakes
- how much alcohol is in budweiser chelada
- all best forrest gump quotes
Some of the rules to write an SQL statement
- SQL statement is not case sensitive you may write the statement in any case (even keywords like SELECT, FROM, DROP etc.) are not case sensitive.
- You can write a complete SQL statement in a single line (but, you should separate them in multiple lines, each clause should be written in separate line).
- You may use to enhance the SQL Query readability.
|SR.NO.||WHERE Clause||HAVING Clause|
|1.||WHERE Clause is used to filter the records from the table based on the specified condition.||HAVING Clause is used to filter record from the groups based on the specified condition.|
|2.||WHERE Clause can be used without GROUP BY Clause||HAVING Clause cannot be used without GROUP BY Clause|
|3.||WHERE Clause implements in row operations||HAVING Clause implements in column operation|
|4.||WHERE Clause cannot contain aggregate function||HAVING Clause can contain aggregate function|
|5.||WHERE Clause can be used with SELECT, UPDATE, DELETE statement.||HAVING Clause can only be used with SELECT statement.|
|6.||WHERE Clause is used before GROUP BY Clause||HAVING Clause is used after GROUP BY Clause|
|7.||WHERE Clause is used with single row function like UPPER, LOWER etc.||HAVING Clause is used with multiple row function like SUM, COUNT etc.|
What is the difference between HAVING and WHERE clause is one the most popular question asked in interviews especially to the freshers? Though these two clauses are very similar and they primarily restrict the tuples returned by the SELECT statement, the main difference arises when used with the GROUP BY clause. So, lets first learn about these two clauses and then their differences.
The WHERE clause is used to fetch the data which specify the given condition. It is used to filter records and select only necessary records. It is used with SELECT, UPDATE, DELETE, etc. query. The SQL also implements and, or, and not in the WHERE clause which is known as the boolean condition.
Example: Take an example of a table Transaction that has ‘Item’, ‘Month’ and ‘Amount’ as attributes.
If we want to calculate the total sale by two products TV and Fridge then our Query statement would be:
SELECT Item, sum(Amount) AS Net_amount
WHERE Item in ( ‘TV’, ‘Fridge’)
GROUP BY Item;
The HAVING clause is generally used along with the GROUP BY clause. This clause is used in the column operation and is applied to aggregate rows or groups according to given conditions.
Example: Consider the above example. Now, if we want to calculate the Net_amount of the total sale by two products TV and Fridge then our query statement would be:
SELECT Item, sum(Amount) AS Net_amount
GROUP BY Item
HAVING Item in (‘TV’, ‘Fridge’);
Difference between WHERE and HAVING clause
- The WHERE clause is used in the selection of rows according to given conditions whereas the HAVING clause is used in column operations and is applied to aggregated rows or groups.
- If GROUP BY is used then it is executed after the WHERE clause is executed in the query. It means it selects the rows before grouping is done or aggregate calculations are performed. Thatswhy, WHERE clause is also called Pre-filter. But, GROUP BY is executed before the execution of the HAVING clause. It means it selects the rows after aggregate calculations are performed. Thatswhy, HAVING clause is also called as Post-filter.
- We cannot use the HAVING clause without SELECT statement whereas the WHERE clause can be used with SELECT, UPDATE, DELETE, etc.
- WE can use aggregate functions like sum, min, max, avg, etc with the HAVING clause but they can never be used with WHERE clause.
- HAVING clause is generally used with the GROUP BY. If you use the HAVING clause without GROUP BY then also it can refer to any column but it won’t be used while performing the query unlike WHERE clause.
Example: The following query has the same results. The WHERE clause uses the “age” index but the having clause will scan the full table instead of directly looking at the “age” column.
- WHERE clause
SELECT * FROM Table WHERE age = 10
2. HAVING clause
SELECT * FROM Table HAVING age = 10
Order of Execution In SQL Server
Before moving on to the main topic, we need to know the execution order of the query in SQL Server. The SQL Server order of execution defines the order in which the clauses of the query are evaluated.
FROM: The logical execution of a SQL Server query begins with the “FROM” statement, which collects data from the tables mentioned in the query.
WHERE: The next step is the “WHERE” clause, which filters the data according to the user-defined condition(s).
GROUP BY: The “GROUP BY” clause performs the grouping of the records (data) obtained from the WHERE condition(s).
HAVING: It’s time to filter the groups based on the specified conditions created by the “GROUP BY” clause. And, this will be done by the HAVING clause.
SELECT: Now, the processing comes down to the SELECT command. And, “SELECT” evaluates which columns will be sent in the result. It also evaluates any keywords such as UNIQUE, DISTINCT, and TOP if it is included.
ORDER BY: Finally, the “ORDER BY” clause is used to sort the data by the column name specified in it. By default, it sorts the data in ascending order.
The HAVING clause is used together with the GROUP BY clause to filter data from groups based on the conditions specified in the HAVING clause. A HAVING clause applies only to groups as a whole.
- HAVING Clause can only be used with a SELECT Statement.
- HAVING Clause is used to filter records from the groups. This means it is used to filter groups.
- HAVING Clause implements in column operations.
- HAVING . Clause is used after GROUP BY Clause.
- HAVING Clause can have aggregate functions.
- The HAVING clause is slower than the WHERE clause and should be avoided whenever possible.
GROUP BY <expression>