Monday, April 3, 2017

How to UNION ALL two or more tables in SQL Server


SQL UNION operator is used to combine the result-set of two or more SELECT statements into single result-set without removing the duplicate rows in the combined result-set. In simple terms, UNION applies DISTINCT on the result-set after combining the results of the SELECT statement.




Basic rules for combining the result sets of two queries by using UNION:

·         Each result-set of SELECT statement within UNION must have the same number of columns.
·         The columns in SELECT statements must also have similar data types.
·         The columns in each SELECT statement must also be in the same order.
·         The combination of result-sets in UNION is based on column position rather than column name.
·         The columns in SELECT statement within UNION do not have to be the same length.
·         Column names for the final result set are taken from the first SELECT statement within the UNION.
·         ORDER BY clauses can only be applied for the combined result-set and not on each individual SELECT result-set within the UNION.
·         GROUP BY and HAVING clauses can only be applied for each individual SELECT result-set within UNION and not for the combined result-set.


Syntax:

      
      SELECT select_column_list FROM table_1
      UNION
      SELECT select_column_list FROM table_2
      .
      .
      .
      UNION
      SELECT select_column_list FROM table_n;


select_column_list is the list of columns for which the data is to be listed.

table_1, table_2, table_n are the name of the tables on which UNION is to be applied.


Example:

        SELECT * FROM Person1
UNION
SELECT * FROM Person2
UNION
SELECT * FROM Person3











Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: