<p>What is the difference between <code>UNION</code> and <code>UNION ALL</code>?</p>

[{'answer_id': 49927, 'author': 'George Mauer', 'author_id': 5056, 'author_profile': 'https://Stackoverflow.com/users/5056', 'pm_score': 5, 'selected': False, 'text': '<blockquote>\n  <p>The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.</p>\n</blockquote>\n\n<p>from <a href="http://zengin.wordpress.com/2007/07/31/union-vs-union-all/" rel="noreferrer">http://zengin.wordpress.com/2007/07/31/union-vs-union-all/</a></p>\n'}, {'answer_id': 49928, 'author': 'Jim Harte', 'author_id': 4544, 'author_profile': 'https://Stackoverflow.com/users/4544', 'pm_score': 12, 'selected': True, 'text': "<p><code>UNION</code> removes duplicate records (where all columns in the results are the same), <code>UNION ALL</code> does not.</p>\n<p>There is a performance hit when using <code>UNION</code> instead of <code>UNION ALL</code>, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).</p>\n<p>To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)</p>\n<h3>UNION Example:</h3>\n<pre><code>SELECT 'foo' AS bar UNION SELECT 'foo' AS bar\n</code></pre>\n<p><strong>Result:</strong></p>\n<pre><code>+-----+\n| bar |\n+-----+\n| foo |\n+-----+\n1 row in set (0.00 sec)\n</code></pre>\n<h3>UNION ALL example:</h3>\n<pre><code>SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar\n</code></pre>\n<p><strong>Result:</strong></p>\n<pre><code>+-----+\n| bar |\n+-----+\n| foo |\n| foo |\n+-----+\n2 rows in set (0.00 sec)\n</code></pre>\n"}, {'answer_id': 49965, 'author': 'Jakub Šturc', 'author_id': 2361, 'author_profile': 'https://Stackoverflow.com/users/2361', 'pm_score': 3, 'selected': False, 'text': '<blockquote>\n  <p>Not sure that it matters which database</p>\n</blockquote>\n\n<p><code>UNION</code> and <code>UNION ALL</code> should work on all SQL Servers.</p>\n\n<p>You should avoid of unnecessary <code>UNION</code>s they are huge performance leak. As a rule of thumb use <code>UNION ALL</code> if you are not sure which to use.</p>\n'}, {'answer_id': 50184, 'author': 'Michiel Overeem', 'author_id': 5043, 'author_profile': 'https://Stackoverflow.com/users/5043', 'pm_score': 5, 'selected': False, 'text': '<p>In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.</p>\n'}, {'answer_id': 92126, 'author': 'mathewbutler', 'author_id': 2288, 'author_profile': 'https://Stackoverflow.com/users/2288', 'pm_score': 6, 'selected': False, 'text': '<p><code>UNION</code> removes duplicates, whereas <code>UNION ALL</code> does not.</p>\n\n<p>In order to remove duplicates the result set must be sorted, and this <em>may</em> have an impact on the performance of the UNION, depending on the volume of data being sorted, and the settings of various RDBMS parameters ( For Oracle <code>PGA_AGGREGATE_TARGET</code> with <code>WORKAREA_SIZE_POLICY=AUTO</code> or <code>SORT_AREA_SIZE</code> and <code>SOR_AREA_RETAINED_SIZE</code> if <code>WORKAREA_SIZE_POLICY=MANUAL</code> ).</p>\n\n<p>Basically, the sort is faster if it can be carried out in memory, but the same caveat about the volume of data applies.</p>\n\n<p>Of course, if you need data returned without duplicates then you <em>must</em> use UNION, depending on the source of your data.</p>\n\n<p>I would have commented on the first post to qualify the "is much less performant" comment, but have insufficient reputation (points) to do so.</p>\n'}, {'answer_id': 11926815, 'author': 'Ihor Vorotnov', 'author_id': 1185107, 'author_profile': 'https://Stackoverflow.com/users/1185107', 'pm_score': 4, 'selected': False, 'text': '<p>You can avoid duplicates and still run much faster than UNION DISTINCT (which is actually same as UNION) by running query like this:</p>\n\n<p><code>SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X</code></p>\n\n<p>Notice the <code>AND a!=X</code> part. This is much faster then UNION.</p>\n'}, {'answer_id': 11991931, 'author': 'DotNetGuy', 'author_id': 1422311, 'author_profile': 'https://Stackoverflow.com/users/1422311', 'pm_score': 5, 'selected': False, 'text': '<p><strong>UNION</strong><br>\nThe <code>UNION</code> command is used to select related information from two tables, much like the <code>JOIN</code> command. However, when using the <code>UNION</code> command all selected columns need to be of the same data type. With <code>UNION</code>, only distinct values are selected.</p>\n\n<p><strong>UNION ALL</strong><br>\nThe <code>UNION ALL</code> command is equal to the <code>UNION</code> command, except that <code>UNION ALL</code> selects all values.</p>\n\n<p>The difference between <code>Union</code> and <code>Union all</code> is that <code>Union all</code> will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.</p>\n\n<p>A <code>UNION</code> statement effectively does a <code>SELECT DISTINCT</code> on the results set. If you know that all the records returned are unique from your union, use <code>UNION ALL</code> instead, it gives faster results.</p>\n'}, {'answer_id': 12884028, 'author': 'Bhaumik Patel', 'author_id': 1218422, 'author_profile': 'https://Stackoverflow.com/users/1218422', 'pm_score': 8, 'selected': False, 'text': '<p>Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.</p>\n\n<ul>\n<li><p>UNION performs a DISTINCT on the result set, eliminating any duplicate rows.</p></li>\n<li><p>UNION ALL does not remove duplicates, and it therefore faster than UNION.</p></li>\n</ul>\n\n<blockquote>\n  <p><strong>Note:</strong> While using this commands all selected columns need to be of the same data type.  </p>\n</blockquote>\n\n<p>Example: If we have two tables, 1) Employee and 2) Customer</p>\n\n<ol>\n<li>Employee table data: </li>\n</ol>\n\n<p><img src="https://i.stack.imgur.com/huYEL.png" alt="enter image description here"></p>\n\n<ol start="2">\n<li>Customer table data:</li>\n</ol>\n\n<p><img src="https://i.stack.imgur.com/FEaKe.png" alt="enter image description here"></p>\n\n<ol start="3">\n<li>UNION Example (It removes all duplicate records):</li>\n</ol>\n\n<p><img src="https://i.stack.imgur.com/lLiS1.png" alt="enter image description here"></p>\n\n<ol start="4">\n<li>UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):</li>\n</ol>\n\n<p><img src="https://i.stack.imgur.com/n5gvq.png" alt="enter image description here"></p>\n'}, {'answer_id': 16959029, 'author': 'Peter Perháč', 'author_id': 81520, 'author_profile': 'https://Stackoverflow.com/users/81520', 'pm_score': 4, 'selected': False, 'text': '<p>Just to add my two cents to the discussion here: one could understand the <code>UNION</code> operator as a pure, SET-oriented UNION - e.g. set A={2,4,6,8}, set B={1,2,3,4}, A UNION B = {1,2,3,4,6,8}</p>\n\n<p>When dealing with sets, you would not want numbers 2 and 4 appearing twice, as an element either <em>is</em> or <em>is not</em> in a set.</p>\n\n<p>In the world of SQL, though, you might want to see all the elements from the two sets together in one "bag" {2,4,6,8,1,2,3,4}. And for this purpose T-SQL offers the operator <code>UNION ALL</code>.</p>\n'}, {'answer_id': 21275382, 'author': 'Pawan Kumar', 'author_id': 2432468, 'author_profile': 'https://Stackoverflow.com/users/2432468', 'pm_score': 1, 'selected': False, 'text': '<p>UNION removes duplicate records in other hand UNION ALL does not. But one need to check the bulk of data that is going to be processed and the column and data type must be same.</p>\n\n<p>since union internally uses "distinct" behavior to select the rows hence it is more costly in terms of time and performance.\nlike</p>\n\n<pre><code>select project_id from t_project\nunion\nselect project_id from t_project_contact  \n</code></pre>\n\n<p>this gives me 2020 records</p>\n\n<p>on other hand</p>\n\n<pre><code>select project_id from t_project\nunion all\nselect project_id from t_project_contact\n</code></pre>\n\n<p>gives me more than 17402 rows</p>\n\n<p>on precedence perspective both has same precedence. </p>\n'}, {'answer_id': 29587511, 'author': 'shA.t', 'author_id': 4519059, 'author_profile': 'https://Stackoverflow.com/users/4519059', 'pm_score': 3, 'selected': False, 'text': "<p>(From Microsoft SQL Server Book Online)</p>\n\n<p><strong>UNION [ALL]</strong> </p>\n\n<blockquote>\n  <p>Specifies that multiple result sets are to be combined and returned as a single result set.</p>\n</blockquote>\n\n<p><strong>ALL</strong> </p>\n\n<blockquote>\n  <p>Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.</p>\n</blockquote>\n\n<p><code>UNION</code> will take too long as a duplicate rows finding like <code>DISTINCT</code> is applied on the results.</p>\n\n<pre><code>SELECT * FROM Table1\nUNION\nSELECT * FROM Table2\n</code></pre>\n\n<p>is equivalent of:</p>\n\n<pre><code>SELECT DISTINCT * FROM (\n    SELECT * FROM Table1\n    UNION ALL\n    SELECT * FROM Table2) DT\n</code></pre>\n\n<hr>\n\n<blockquote>\n  <p>A side effect of applying <code>DISTINCT</code> over results is a <strong>sorting operation</strong> on results.</p>\n</blockquote>\n\n<p><code>UNION ALL</code> results will be shown as <em>arbitrary</em> order on results But <code>UNION</code> results will be shown as <code>ORDER BY 1, 2, 3, ..., n (n = column number of Tables)</code> applied on results. You can see this side effect when you don't have any duplicate row.</p>\n"}, {'answer_id': 30544200, 'author': 'Rahul Sawant', 'author_id': 4592066, 'author_profile': 'https://Stackoverflow.com/users/4592066', 'pm_score': 1, 'selected': False, 'text': '<p>One more thing i would like to add-</p>\n\n<p><strong>Union</strong>:- Result set is sorted in ascending order.</p>\n\n<p><strong>Union All</strong>:- Result set is not sorted. two Query output just gets appended. </p>\n'}, {'answer_id': 35772419, 'author': 'AjV Jsy', 'author_id': 2078245, 'author_profile': 'https://Stackoverflow.com/users/2078245', 'pm_score': 1, 'selected': False, 'text': "<p>If there is no <code>ORDER BY</code>, a <code>UNION ALL</code> may bring rows back as it goes, whereas a <code>UNION</code> would make you wait until the very end of the query before giving you the whole result set at once. This can make a difference in a time-out situation - a <code>UNION ALL</code> keeps the connection alive, as it were.</p>\n\n<p>So if you have a time-out issue, and there's no sorting, and duplicates aren't an issue, <code>UNION ALL</code> may be rather helpful.</p>\n"}, {'answer_id': 37267992, 'author': 'Pedram', 'author_id': 1156018, 'author_profile': 'https://Stackoverflow.com/users/1156018', 'pm_score': 2, 'selected': False, 'text': "<p><strong><em><code>UNION</code> merges the contents of two structurally-compatible tables into a single combined table.</em></strong> </p>\n\n<ul>\n<li>Difference:</li>\n</ul>\n\n<p><em>The difference between <code>UNION</code> and <code>UNION ALL</code> is that <code>UNION will</code> omit duplicate records whereas <code>UNION ALL</code> will include duplicate records.</em></p>\n\n<p><em><code>Union</code> Result set is sorted in ascending order whereas <code>UNION ALL</code> Result set is not sorted</em></p>\n\n<p><code>UNION</code> performs a <code>DISTINCT</code> on its Result set so it will eliminate any duplicate rows. Whereas <code>UNION ALL</code> won't remove duplicates and therefore it is faster than <code>UNION</code>.*</p>\n\n<p><strong><em>Note</em>:</strong> <em>The performance of <code>UNION ALL</code> will typically be better than <code>UNION</code>, since <code>UNION</code> requires the server to do the additional work of removing any duplicates. So, in cases where it is certain that there will not be any duplicates, or where having duplicates is not a problem, use of <code>UNION ALL</code> would be recommended for performance reasons.</em></p>\n"}, {'answer_id': 37672576, 'author': 'reza.cse08', 'author_id': 2597706, 'author_profile': 'https://Stackoverflow.com/users/2597706', 'pm_score': 2, 'selected': False, 'text': '<p>Suppose that you have two table <strong>Teacher</strong> &amp; <strong>Student</strong></p>\n\n<p>Both have <strong>4 Column with different Name</strong> like this</p>\n\n<pre><code>Teacher - ID(int), Name(varchar(50)), Address(varchar(50)), PositionID(varchar(50))\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/8uYB7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8uYB7.png" alt="enter image description here"></a></p>\n\n<pre><code>Student- ID(int), Name(varchar(50)), Email(varchar(50)), PositionID(int)\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/lyCoB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lyCoB.png" alt="enter image description here"></a></p>\n\n<p><strong>You can apply UNION or UNION ALL for those two table which have same number of columns. But they have different name or data type.</strong></p>\n\n<p>When you apply <code>UNION</code> operation on 2 tables, it neglects all duplicate entries(all columns value of row in a table is same of another table). Like this</p>\n\n<pre><code>SELECT * FROM Student\nUNION\nSELECT * FROM Teacher\n</code></pre>\n\n<p><strong>the result will be</strong></p>\n\n<p><a href="https://i.stack.imgur.com/FNzdi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FNzdi.png" alt="enter image description here"></a></p>\n\n<p>When you apply <code>UNION ALL</code> operation on 2 tables, it returns all entries with duplicate(if there is any difference between any column value of a row in 2 tables). Like this</p>\n\n<pre><code>SELECT * FROM Student\nUNION ALL\nSELECT * FROM Teacher\n</code></pre>\n\n<p><strong>Output</strong>\n<a href="https://i.stack.imgur.com/DTBLt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DTBLt.png" alt="enter image description here"></a></p>\n\n<p><strong>Performance:</strong></p>\n\n<p>Obviously <strong>UNION ALL</strong> performance is better that <strong>UNION</strong> as they do additional task to remove the duplicate values. You can check that from <strong>Execution Estimated Time</strong> by press <strong>ctrl+L</strong> at <strong>MSSQL</strong></p>\n'}, {'answer_id': 37950816, 'author': 'DBA', 'author_id': 6481481, 'author_profile': 'https://Stackoverflow.com/users/6481481', 'pm_score': 4, 'selected': False, 'text': '<p>UNION - results in <strong>distinct</strong> records <br><br> while <br><br> \nUNION ALL - results in all the records including duplicates.</p>\n\n<p>Both are blocking operators and hence I personally prefer using JOINS over Blocking Operators(UNION, INTERSECT, UNION ALL etc. ) anytime.</p>\n\n<p>To illustrate why Union operation performs poorly in comparison to Union All checkout the following example.</p>\n\n<pre><code>CREATE TABLE #T1 (data VARCHAR(10))\n\nINSERT INTO #T1\nSELECT \'abc\'\nUNION ALL\nSELECT \'bcd\'\nUNION ALL\nSELECT \'cde\'\nUNION ALL\nSELECT \'def\'\nUNION ALL\nSELECT \'efg\'\n\n\nCREATE TABLE #T2 (data VARCHAR(10))\n\nINSERT INTO #T2\nSELECT \'abc\'\nUNION ALL\nSELECT \'cde\'\nUNION ALL\nSELECT \'efg\'\n</code></pre>\n\n<p><a href="https://i.stack.imgur.com/kdD4p.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/kdD4p.jpg" alt="enter image description here"></a></p>\n\n<p><strong>Following are results of UNION ALL and UNION operations.</strong></p>\n\n<p><a href="https://i.stack.imgur.com/LpjWD.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/LpjWD.jpg" alt="enter image description here"></a></p>\n\n<p><strong>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.</strong>  </p>\n\n<p>Using UNION results in <strong>Distinct Sort</strong> operations in the Execution Plan. Proof to prove this statement is shown below:</p>\n\n<p><a href="https://i.stack.imgur.com/QmQlL.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/QmQlL.jpg" alt="enter image description here"></a></p>\n'}, {'answer_id': 43648727, 'author': 'James Grey', 'author_id': 3728901, 'author_profile': 'https://Stackoverflow.com/users/3728901', 'pm_score': 3, 'selected': False, 'text': "<p>I add an example, </p>\n\n<p><strong>UNION</strong>, it is merging with distinct --> slower, because it need comparing (In Oracle SQL developer, choose query, press F10 to see cost analysis).</p>\n\n<p><strong>UNION ALL</strong>, it is merging without distinct --> faster.</p>\n\n<pre><code>SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual\nUNION\nSELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;\n</code></pre>\n\n<p>and</p>\n\n<pre><code>SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual\nUNION ALL\nSELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;\n</code></pre>\n"}, {'answer_id': 53721250, 'author': 'Aris Mist', 'author_id': 9075992, 'author_profile': 'https://Stackoverflow.com/users/9075992', 'pm_score': 1, 'selected': False, 'text': "<p>Important! Difference between Oracle and Mysql: Let's say that t1 t2 don't have duplicate rows between them but they have duplicate rows individual. Example: t1 has sales from 2017 and t2 from 2018</p>\n\n<pre><code>SELECT T1.YEAR, T1.PRODUCT FROM T1\n\nUNION ALL\n\nSELECT T2.YEAR, T2.PRODUCT FROM T2\n</code></pre>\n\n<p>In ORACLE UNION ALL fetches all rows from both tables. The same will occur in MySQL.</p>\n\n<p><strong>However:</strong></p>\n\n<pre><code>SELECT T1.YEAR, T1.PRODUCT FROM T1\n\nUNION\n\nSELECT T2.YEAR, T2.PRODUCT FROM T2\n</code></pre>\n\n<p>In <strong>ORACLE</strong>, UNION fetches all rows from both tables because there are no duplicate values between t1 and t2. On the other hand in <strong>MySQL</strong> the resultset will have fewer rows because there will be duplicate rows within table t1 and also within table t2!</p>\n"}, {'answer_id': 58480245, 'author': 'Dowlers', 'author_id': 223139, 'author_profile': 'https://Stackoverflow.com/users/223139', 'pm_score': 0, 'selected': False, 'text': '<p><code>UNION ALL</code> also works on more data types as well. For example when trying to union spatial data types. For example: </p>\n\n<pre><code>select a.SHAPE from tableA a\nunion\nselect b.SHAPE from tableB b\n</code></pre>\n\n<p>will throw</p>\n\n<p><code>The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.</code></p>\n\n<p>However <code>union all</code> will not.</p>\n'}]
['https://Stackoverflow.com/questions/49925', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3208/']