“Union of ideas begets a surprising confidence” – J. Madison
Today’s post will deal with an SQL command many of us may be familiar with but may forget exists, or haven’t thought about other uses for it.
UNION
In simplest terms the UNION command allows you to join separate SQL queries into one result set. The UNION command has one optional option “ALL”
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.
UNION Example
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SELECT p506.rmatp AS RefMaterial_506 ,p506.knumh AS ConditionRecord_506 ,kondp.packnr AS PackingInst_506 ,p506.werks AS Plant_506 ,RIGHT(p506.matnr,6) AS MaterialNo_506 FROM dgSAP.dbo.kotp506 AS p506 LEFT OUTER JOIN dgSAP.dbo.kondp AS kondp ON p506.knumh = kondp.knumh WHERE p506.kschl='ZRMA' -- Condition type AND p506.kappl='PO' -- Application AND p506.datbi ='99991231' -- valid to AND p506.werks = N'7716' -- plant UNION ALL SELECT p501.rmatp AS RefMaterial_501 ,p501.knumh AS ConditionRecord_501 ,kondp.packnr AS PackingInst_501 ,p501.werks AS Plant_501 ,'blank' AS MaterialNo_501 FROM dgSAP.dbo.kotp501 AS p501 LEFT OUTER JOIN dgSAP.dbo.kondp AS kondp ON p501.knumh = kondp.knumh WHERE p501.kschl='ZRMA' -- Condition type AND p501.kappl='PO' -- Application AND p501.datbi ='99991231' -- valid to AND p501.werks = N'7716' -- plant |
Explanation
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 ‘blank’ 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.
There 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.
The WITH command allows you to store the results of a query temporarily in memory or in TSQL’s 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.
Leave a Reply