What are the differences between
WHERE and HAVING clauses?
- WHERE filters rows
before aggregation (GROUPING), where as HAVING filters groups after the
aggregations are performed.
- WHERE clause can be used
with SELECT, INSERT, and UPDATE statements, where as HAVING clause can
only be used with the SELECT statement.
- Aggregate functions cannot be used in the WHERE clause whereas aggregate functions can be used in HAVING clause.
What
exactly are UNION and UNION All?
UNION and UNION
ALL operators in SQL Server are used to combine the result sets of two or more
SELECT queries.
Let’s understand what is meant by this with an example. We have
2 tables here,tblIndiaCustomers and tblUSCustomers. Both of these tables have identical columns
– Id,Name and Email.
Now if we issue a select query against these 2 tables and
execute them, we will get 2 separate result sets. We will get 2 rows each from tblIndiaCustomers and tblUSCustomers.
Now we want to combine both of these result sets into one result
set. How do we do that? We can use UNION or UNION ALL Operators. Let’s first use UNION ALL and
see what will happen. When we execute the query, it is going to combine the
rows fromtblndiaCustomers with rows from tblUSCustomers. So while using UNION ALL, we are combining the result sets
from these two queries. Here in output, we will get 4 rows.
Then instead of using UNION ALL, just use UNION and see what
will happen. Now we will get only 3 rows. While looking at the output, we can
see that the the duplicate record –Name: Thomas,
Email: T@T.Com is eliminated. While using UNION ALL, we will get all the rows including
duplicates. While using UNION Operator, the output is sorted as well
