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,
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.
More post for you if you are interested:
Difference between Where and Having Clause in SQL :
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
FROM Transaction
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
FROM Transaction
GROUP BY Item
HAVING Item in (‘TV’, ‘Fridge’);
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.
SELECT * FROM Table WHERE age = 10
2. HAVING clause
SELECT * FROM Table HAVING age = 10
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.
Key Points
Syntax
SELECT <column_list>
FROM <table_name>
WHERE <search_condition(s)>
GROUP BY <expression>
HAVING <condition>;
Dior Sauvage Elixir HI WELCOME TO solsarin.Dior Sauvage Elixir is a fragrance from the renowned…
ORTO PARISI Megamare HI WELCOME TO solsarin.ORTO PARISI Megamare is a masculine fragrance. This fragrance…
Creed Aventus perfume hi welcome to solsarin.With Creed Aventus perfume, you can experience elegance, luxury,…
Sauvage Dior cologne hi welcome to solsarin.A captivating scent awaits you with Sauvage Dior cologne! If…
Nasomatto perfume hi welcome to solsarin.Introducing Nasomatto Perfume: A True Luxury Experience If you are…
carolina herrera perfume hi welcome to solsarin.Enjoy an enchanting symphony of scents inspired by the…