What is the difference between UNION and UNION ALL

Question

User · Answer

UNION - results in distinct records  while   UNION ALL - results in all the records including duplicates   Both are blocking operators and hence I personally prefer using JOINS over Blocking Operators UNION  INTERSECT  UNION ALL etc    anytime   To illustrate why Union operation performs poorly in comparison to Union All checkout the following example   CREATE TABLE  T1  data VARCHAR 10    INSERT INTO  T1 SELECT  abc  UNION ALL SELECT  bcd  UNION ALL SELECT  cde  UNION ALL SELECT  def  UNION ALL SELECT  efg    CREATE TABLE  T2  data VARCHAR 10    INSERT INTO  T2 SELECT  abc  UNION ALL SELECT  cde  UNION ALL SELECT  efg      Following are results of UNION ALL and UNION operations     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     Using UNION results in Distinct Sort operations in the Execution Plan  Proof to prove this statement is shown below

User · Answer

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    from http   zengin wordpress com 2007 07 31 union-vs-union-all

User · Answer

UNION merges the contents of two structurally-compatible tables into a single combined table     Difference    The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records   Union Result set is sorted in ascending order whereas UNION ALL Result set is not sorted  UNION performs a DISTINCT on its Result set so it will eliminate any duplicate rows  Whereas UNION ALL won t remove duplicates and therefore it is faster than UNION    Note  The performance of UNION ALL will typically be better than UNION  since UNION 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 UNION ALL would be recommended for performance reasons

User · Answer

UNION removes duplicate records  where all columns in the results are the same   UNION ALL does not  There is a performance hit when using UNION instead of UNION ALL  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   UNION Example  SELECT  foo  AS bar UNION SELECT  foo  AS bar  Result   -----    bar    -----    foo    -----  1 row in set  0 00 sec   UNION ALL example  SELECT  foo  AS bar UNION ALL SELECT  foo  AS bar  Result   -----    bar    -----    foo     foo    -----  2 rows in set  0 00 sec

User · Answer

UNION The UNION command is used to select related information from two tables  much like the JOIN command  However  when using the UNION command all selected columns need to be of the same data type  With UNION  only distinct values are selected   UNION ALL The UNION ALL command is equal to the UNION command  except that UNION ALL selects all values   The difference between Union and Union all is that Union all will not eliminate duplicate rows  instead it just pulls all rows from all tables fitting your query specifics and combines them into a table   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

User · Answer

Not sure that it matters which database   UNION and UNION ALL should work on all SQL Servers   You should avoid of unnecessary UNIONs they are huge performance leak  As a rule of thumb use UNION ALL if you are not sure which to use

User · Answer

One more thing i would like to add-  Union - Result set is sorted in ascending order   Union All - Result set is not sorted  two Query output just gets appended

User · Answer

I add an example    UNION  it is merging with distinct --  slower  because it need comparing  In Oracle SQL developer  choose query  press F10 to see cost analysis    UNION ALL  it is merging without distinct --  faster   SELECT to date sysdate   yyyy-mm-dd   FROM dual UNION SELECT to date sysdate   yyyy-mm-dd   FROM dual    and  SELECT to date sysdate   yyyy-mm-dd   FROM dual UNION ALL SELECT to date sysdate   yyyy-mm-dd   FROM dual

User · Answer

Both UNION and UNION ALL concatenate the result of two different SQLs  They differ in the way they handle duplicates    UNION performs a DISTINCT on the result set  eliminating any duplicate rows  UNION ALL does not remove duplicates  and it therefore faster than UNION       Note  While using this commands all selected columns need to be of the same data type      Example  If we have two tables  1  Employee and 2  Customer   Employee table data        Customer table data       UNION Example  It removes all duplicate records        UNION ALL Example  It just concatenate records  not eliminate duplicates  so it is faster than UNION

User · Answer

In ORACLE  UNION does not support BLOB  or CLOB  column types  UNION ALL does

User · Answer

union is used to select distinct values from two tables  where as union all is used to select all values including  duplicates from the tables

User · Answer

Suppose that you have two table Teacher  amp  Student  Both have 4 Column with different Name like this  Teacher - ID int   Name varchar 50    Address varchar 50    PositionID varchar 50       Student- ID int   Name varchar 50    Email varchar 50    PositionID int      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   When you apply UNION operation on 2 tables  it neglects all duplicate entries all columns value of row in a table is same of another table   Like this  SELECT   FROM Student UNION SELECT   FROM Teacher   the result will be    When you apply UNION ALL 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  SELECT   FROM Student UNION ALL SELECT   FROM Teacher   Output   Performance   Obviously UNION ALL performance is better that UNION as they do additional task to remove the duplicate values  You can check that from Execution Estimated Time by press ctrl L at MSSQL

User · Answer

Just to add my two cents to the discussion here  one could understand the UNION 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   When dealing with sets  you would not want numbers 2 and 4 appearing twice  as an element either is or is not in a set   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 UNION ALL

User · Answer

From Microsoft SQL Server Book Online   UNION  ALL       Specifies that multiple result sets are to be combined and returned as a single result set    ALL      Incorporates all rows into the results  This includes duplicates  If not specified  duplicate rows are removed    UNION will take too long as a duplicate rows finding like DISTINCT is applied on the results   SELECT   FROM Table1 UNION SELECT   FROM Table2   is equivalent of   SELECT DISTINCT   FROM       SELECT   FROM Table1     UNION ALL     SELECT   FROM Table2  DT        A side effect of applying DISTINCT over results is a sorting operation on results    UNION ALL results will be shown as arbitrary order on results But UNION results will be shown as ORDER BY 1  2  3       n  n   column number of Tables  applied on results  You can see this side effect when you don t have any duplicate row

User · Answer

It is good to understand with a Venn diagramm    here is the link to the source  There is a good description

User · Answer

If there is no ORDER BY  a UNION ALL may bring rows back as it goes  whereas a UNION 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 UNION ALL keeps the connection alive  as it were   So if you have a time-out issue  and there s no sorting  and duplicates aren t an issue  UNION ALL may be rather helpful

User · Answer

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   since union internally uses  distinct  behavior to select the rows hence it is more costly in terms of time and performance  like  select project id from t project union select project id from t project contact     this gives me 2020 records  on other hand  select project id from t project union all select project id from t project contact   gives me more than 17402 rows  on precedence perspective both has same precedence

User · Answer

You can avoid duplicates and still run much faster than UNION DISTINCT  which is actually same as UNION  by running query like this   SELECT   FROM mytable WHERE a X UNION ALL SELECT   FROM mytable WHERE b Y AND a  X  Notice the AND a  X part  This is much faster then UNION

User · Answer

UNION and UNION ALL used to combine two or more query results     UNION command selects distinct and related information from two tables which will eliminates duplicate rows    On the other hand  UNION ALL command selects all the values from both the tables  which displays all rows

User · Answer

UNION ALL also works on more data types as well  For example when trying to union spatial data types  For example    select a SHAPE from tableA a union select b SHAPE from tableB b   will throw  The data type geometry cannot be used as an operand to the UNION  INTERSECT or EXCEPT operators because it is not comparable   However union all will not

User · Answer

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  SELECT T1 YEAR  T1 PRODUCT FROM T1  UNION ALL  SELECT T2 YEAR  T2 PRODUCT FROM T2   In ORACLE UNION ALL fetches all rows from both tables  The same will occur in MySQL   However   SELECT T1 YEAR  T1 PRODUCT FROM T1  UNION  SELECT T2 YEAR  T2 PRODUCT FROM T2   In ORACLE  UNION fetches all rows from both tables because there are no duplicate values between t1 and t2  On the other hand in MySQL the resultset will have fewer rows because there will be duplicate rows within table t1 and also within table t2

User · Answer

UNION removes duplicates  whereas UNION ALL does not   In order to remove duplicates the result set must be sorted  and this may 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 PGA AGGREGATE TARGET with WORKAREA SIZE POLICY AUTO or SORT AREA SIZE and SOR AREA RETAINED SIZE if WORKAREA SIZE POLICY MANUAL     Basically  the sort is faster if it can be carried out in memory  but the same caveat about the volume of data applies   Of course  if you need data returned without duplicates then you must use UNION  depending on the source of your data   I would have commented on the first post to qualify the  is much less performant  comment  but have insufficient reputation  points  to do so

[sql] What is the difference between UNION and UNION ALL?

Examples related to sql

Examples related to union

Examples related to union-all