Working With Unions in SQL Server

Question

Hi friends I want to know how to work with unions in SQL Server. Thank you.

Keyur Narkhi · Answer

Hi Please follow below link http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/

MuthuMari M · Answer

Hi, union when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables. The following are basic rules for combining the result sets of two queries by using UNION: The number and the order of the columns must be the same in all queries. The data types must be compatible. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values UNION: This will remove duplicate values UNION ALL: This will repeat values thanks. If this post is useful then mark it as " Accepted Answer "

Senthilkumar · Answer

Hi Vipendra, In SQL Server you have the ability to combine multiple datasets into one comprehensive dataset by using the UNION or UNION ALL operators. There is a big difference in how these work as well as the final result set that is returned, but basically these commands join multiple datasets that have similar structures into one combined dataset. Here is a brief description: UNION - this command will allow you to join multiple datasets into one dataset and will remove any duplicates that exist. Basically it is performing a DISTINCT operation across all columns in the result set. UNION ALL - this command again allows you to join multiple datasets into one dataset, but it does not remove any duplicate rows. Because this does not remove duplicate rows this process is faster, but if you don't want duplicate records you will need to use the UNION operator instead. Rules to union data: Each query must have the same number of columns Each column must have compatible data types Column names for the final result set are taken from the first query ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set Please go this url for more detail http://www.mssqltips.com/sqlservertip/1387/joining-data-using-union-and-union-all-in-sql-server/

Satyapriya Nayak · Answer

Hi Vipendra, SQL UNION query is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT). The syntax is as follows: [SQL Statement 1] UNION [SQL Statement 2] Say we have the following two tables, Table Store_Information store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Table Internet_Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750 and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement: SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales Result: Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999 SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values. The syntax for UNION ALL is as follows: [SQL Statement 1] UNION ALL [SQL Statement 2] Let's use the same example as the previous section to illustrate the difference. Assume that we have the following two tables, Table Store_Information store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Table Internet_Sales Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750 and we want to find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet. To do so, we use the following SQL statement: SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales Result: Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-08-1999 Jan-07-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999 Thanks

Kunal Vaishya · Answer

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results. Example: Table 1 : First,Second,Third,Fourth,Fifth Table 2 : First,Second,Fifth,Sixth Result Set: UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values) UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)

Mahak Gupta · Answer

Read this article: http://www.w3schools.com/sql/sql_union.asp

Working With Unions in SQL Server

Answers (6)