{"id":85,"date":"2017-10-30T20:21:11","date_gmt":"2017-10-30T20:21:11","guid":{"rendered":"http:\/\/eipsoftware.com\/musings\/?p=85"},"modified":"2018-02-19T16:40:30","modified_gmt":"2018-02-19T16:40:30","slug":"sql-union","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/sql-union\/","title":{"rendered":"SQL Union – New Ideas"},"content":{"rendered":"

\u201cUnion of ideas begets a surprising confidence\u201d<\/em> \u2013 J. Madison<\/p>\n

Today\u2019s post will deal with an SQL command many of us may be familiar with but may forget exists, or haven\u2019t thought about other uses for it.<\/p>\n

UNION<\/h4>\n

In simplest terms the UNION command allows you to join separate SQL queries into one result set. The UNION command has one optional option \u201cALL\u201d<\/p>\n

The difference between UNION and UNION ALL is that the UNION command will remove duplicates rows. While the UNION ALL command will keep all rows from each SQL query. You can combine 2 or more SQL queries, and the queries can be from the same table or different tables. You can nest UNION commands. One of the limitations is that each of the SQL queries must have the same number of columns in its results set. You can use a blank or NULL value to pad the queries in your UNION query.<\/p>\n

UNION Example<\/h4>\n
SELECT\t\tp506.rmatp\t\t\tAS RefMaterial_506\t\t\r\n\t\t,p506.knumh\t\t\tAS ConditionRecord_506\r\n\t\t,kondp.packnr\t\t\tAS PackingInst_506\t\r\n\t\t,p506.werks\t\t\tAS Plant_506\r\n\t\t,RIGHT(p506.matnr,6)\tAS MaterialNo_506\r\n\t\t\t\t\t\t\t\r\nFROM\t\tdgSAP.dbo.kotp506 AS p506\r\n\t\tLEFT OUTER JOIN dgSAP.dbo.kondp AS kondp\r\n\t\t\tON p506.knumh = kondp.knumh\r\n\t\t \r\nWHERE\t\tp506.kschl='ZRMA'\t\t\t-- Condition type\r\n\t\tAND p506.kappl='PO'\t\t\t-- Application\r\n\t\tAND p506.datbi ='99991231'\t\t-- valid to\r\n\t\tAND p506.werks = N'7716'\t\t-- plant\t\t\t\t\r\n\r\nUNION ALL\r\n\r\nSELECT\t\tp501.rmatp\t\t\tAS RefMaterial_501\t\t\r\n\t\t,p501.knumh\t\t\tAS ConditionRecord_501\t\r\n\t\t,kondp.packnr\t\t\tAS PackingInst_501\r\n\t\t,p501.werks\t\t\tAS Plant_501\r\n\t\t,'blank'\t\t\tAS MaterialNo_501\r\n\t\t\t\t\t\t\t\t\t\t\r\nFROM\t\tdgSAP.dbo.kotp501 AS p501\r\n\t\tLEFT OUTER JOIN dgSAP.dbo.kondp AS kondp\r\n\t\t\tON p501.knumh = kondp.knumh\r\n\t\t\t \r\nWHERE\t\tp501.kschl='ZRMA'\t\t\t-- Condition type\r\n\t\tAND p501.kappl='PO'\t\t\t-- Application\r\n\t\tAND p501.datbi ='99991231'\t\t-- valid to\r\n\t\tAND p501.werks = N'7716'\t\t-- plant<\/pre>\n
 <\/p>\n
Explanation<\/h4>\n
In the example above you can see that I put the UNION command between the two queries. And I pad the second query by putting the word \u2018blank\u2019 in the second query. Another thing to note is that the column names will be the values in the first query of the UNION query.
\nThere are dozens of uses for using the UNION command, but one of my more common uses is when I want to avoid rerunning sub-queries and create a temporary table using the WITH command.<\/p>\n
The WITH command allows you to store the results of a query temporarily in memory or in TSQL\u2019s temp database depending on the size of the results. By storing the results temporarily and not having to rerun them multiple times during the query processing, it will reduce the query execution time. Additionally if the results are small and can be stored in memory it can significantly speed up the query time.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u201cUnion of ideas begets a surprising confidence\u201d \u2013 J. Madison Today\u2019s post will deal with an SQL command many of us may be familiar with but may forget exists, or haven\u2019t thought about other uses for it. UNION In simplest terms the UNION command allows you to join separate SQL queries into one result set. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,4,6],"tags":[13,14,15,18],"series":[],"class_list":["post-85","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-code","category-sql","tag-hint","tag-sql","tag-t-sql","tag-union"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":4,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":146,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/85\/revisions\/146"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=85"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}