UNION : UNION Statement in SQL Server combines all rows in the results of two queries.<\/p>\n
EXAMPLE’s Given below:<\/strong><\/p>\n [sql] Result:<\/p>\n ———– (2 row(s) affected)<\/p>\n Result is the combining the results of two select queries.<\/p>\n [sql] Result:<\/p>\n —– (3 row(s) affected)<\/p>\n Result is the combining the results of three select queries.<\/p>\n [sql] result: (2 row(s) affected)<\/p>\n Result is the combining the results of three select queries, it can be observed that the duplicate rows in the select queries are ignored.<\/p>\n [sql] DECLARE @table2 TABLE ( DECLARE @i AS INT<\/p>\n SET @i = 1<\/p>\n WHILE @i <= 10 SET @i = 5<\/p>\n WHILE @i <= 15 SELECT * SELECT * SELECT * id id id @table1 contains numbers 1 to 10, @table2 contains numbers 5 to 15 So union statement works as follows:<\/em> Note:<\/strong> Make sure that the column datatypes are same in each select statements, if not you would get the error<\/em> The column names in the first select statement are used in the output of union statement. Use alias names if you wanted to change the column names. Fixed code: Applicable to SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 r2, SQL Server 2012<\/p>\n","protected":false},"excerpt":{"rendered":" UNION : UNION Statement in SQL Server combines all rows in the results of two queries. EXAMPLE’s Given below: [sql] SELECT 1 UNION SELECT 2 [\/sql] Result: ———– 1 2 (2 row(s) affected) Result is the combining the results of two select queries. [sql] SELECT ‘Apple’ UNION SELECT ‘Mango’ UNION SELECT ‘Grape’ [\/sql] Result: —–…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,1789],"tags":[1818,1819,1820,1821,1822,1823,1824],"class_list":["post-179","post","type-post","status-publish","format-standard","hentry","category-sql-server-basics","category-t-sql-code-examples","tag-sql-server-2000-union-example","tag-sql-server-2005-union-example","tag-sql-server-2008-union-example","tag-sql-server-2012-union-example","tag-sql-server-union","tag-sql-server-union-errors","tag-sql-server-union-example"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSELECT 1
\nUNION
\nSELECT 2
\n[\/sql]<\/p>\n
\n1
\n2<\/p>\n
\nSELECT ‘Apple’
\nUNION
\nSELECT ‘Mango’
\nUNION
\nSELECT ‘Grape’
\n[\/sql]<\/p>\n
\nApple
\nGrape
\nMango<\/p>\n
\nSELECT ‘Apple’
\nUNION
\nSELECT ‘Apple’
\nUNION
\nSELECT ‘Grape’
\n[\/sql]<\/p>\n
\n—–
\nApple
\nGrape<\/p>\n
\nSET NOCOUNT ON
\nDECLARE @table1 TABLE (
\n id INT)<\/p>\n
\n id INT)<\/p>\n
\n BEGIN
\n INSERT INTO @table1
\n SELECT @i
\n SET @i = @i + 1
\n END<\/p>\n
\n BEGIN
\n INSERT INTO @table2
\n SELECT @i
\n SET @i = @i + 1
\n END<\/p>\n
\nFROM @table1<\/p>\n
\nFROM @table2<\/p>\n
\nFROM @table1
\nUNION
\nSELECT *
\nFROM @table2
\n[\/sql]
\nResult:<\/p>\n
\n———–
\n1
\n2
\n3
\n4
\n5
\n6
\n7
\n8
\n9
\n10<\/p>\n
\n———–
\n5
\n6
\n7
\n8
\n9
\n10
\n11
\n12
\n13
\n14
\n15<\/p>\n
\n———–
\n1
\n2
\n3
\n4
\n5
\n6
\n7
\n8
\n9
\n10
\n11
\n12
\n13
\n14
\n15<\/p>\n
\nunion of @table1 and @table2 contains numbers 1 to 15 (with out dublicate values)<\/p>\n
\nIf two queries are unioned and if the query 1 results in x rows and query 2 in y rows of which z rows are dupicate with respect to query1,
\nthen union gives x+y-z rows.<\/p>\n
\nMake sure that the select statements fetch the same number of columns, if not you would get the error<\/em>
\n
\nMsg 205, Level 16, State 1, Line 1
\nAll queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists
\n<\/font>
\nSample Code:
\n[sql]
\nSELECT ‘Apple’
\nUNION
\nSELECT ‘Apple’,’Grape’
\n[\/sql]<\/p>\n
\n
\nMsg 245, Level 16, State 1, Line 1
\nConversion failed when converting the varchar value ‘two’ to data type int.
\n<\/font>
\nSample Code:
\n[sql]
\nSELECT ‘Apple’,1
\nUNION
\nSELECT ‘Grape’,’two’
\n[\/sql]<\/p>\n
\nIf you are specifng values directly in select queries then use alias names for columns.
\nSample queries:
\n[sql]
\nSELECT * INTO #a FROM (
\nSELECT ‘Apple’ ,1
\nUNION
\nSELECT ‘Grape’,’2′) a
\n[\/sql]
\nOutput:
\n
\nMsg 8155, Level 16, State 2, Line 4
\nNo column name was specified for column 1 of ‘a’.
\nMsg 8155, Level 16, State 2, Line 4
\nNo column name was specified for column 2 of ‘a’.
\nMsg 1038, Level 15, State 5, Line 1
\nAn object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
\nMsg 1038, Level 15, State 5, Line 1
\nAn object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Change the alias to a valid name.
\n<\/font><\/p>\n
\n[sql]
\nSELECT * INTO #a FROM (
\nSELECT ‘Apple’ a1 ,1 a2
\nUNION
\nSELECT ‘Grape’,’2′) a
\n[\/sql]<\/p>\n