Thursday, August 21, 2014

What are the differences between WHERE and HAVING clauses?

  1. WHERE filters rows before aggregation (GROUPING), where as HAVING filters groups after the aggregations are performed.
  2. WHERE clause can be used with SELECT, INSERT, and UPDATE statements, where as HAVING clause can only be used with the SELECT statement.
  3. Aggregate functions cannot be used in the WHERE clause whereas aggregate functions can be used in HAVING clause.
          @@@@@@@@@@@@@@@END@@@@@@@@@@@@@@

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.
0.Customers
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.
1
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.
2
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

No comments:

Post a Comment