Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,457,274 Views

UNION vs UNION ALL

Posted by decipherinfosys on February 16, 2007

This is another one of those small things that beginners usually make a mistake in.  UNION and UNION ALL are both used to club together results from different sets.  UNION eliminates the duplicates between the two sets that it is joining while UNION ALL preserves the duplicates.  Example:  Say a table “TableA” has 2000 records:
Select a, b, c from tableA

union

select a, b, c from tableA

This will return back 2000 records.  If you had used UNION ALL instead of UNION, it would have returned back 4000 records.

The other key things are:

1) the number of columns should be the same in all the sets (i.e. one set cannot have 3 columns while the other one has 4 –> you will get an error),
2) the data types should be the same for the columns in all the sets (for the same order of columns) else implicit data-type conversions can cause issues

Example (SQL Server syntax):

CREATE TABLE T1 (COL1 INT, COL2 VARCHAR(10), COL3 DATETIME)
GO
CREATE TABLE T2 (COL1 INT, COL2 SQL_VARIANT, COL3 NUMERIC(8,3))
GO
INSERT INTO T1 VALUES (1, ‘ABC’, GETDATE())
INSERT INTO T1 VALUES (2, ‘XYZ’, GETDATE() + 1)

INSERT INTO T2 VALUES (1, ‘ABC’, 123.12)
INSERT INTO T2 VALUES (2, GETDATE(), 12345.21)
GO

–see the values in T2
–Note the numeric values in the last column

SELECT COL1, COL2, COL3 FROM T2
go

COL1        COL2                     COL3
———– ————————- ———-
1           ABC                      123.120
2           2007-01-21 18:39:23.990  12345.210

/*
Now, since the data-types are incompatible between the same
column order columns in the 2 sets, implicit conversion forces
the numeric data types to change
*/
SELECT COL1, COL2, COL3 FROM T1
UNION ALL
SELECT COL1, COL2, COL3 FROM T2
GO

COL1        COL2                    COL3
———– ———————– ——————————————————
1           ABC                     2007-01-21 18:39:23.990
2           XYZ                     2007-01-22 18:39:23.990
1           ABC                     1900-05-04 02:52:48.000
2           2007-01-21 18:39:23.990 1933-10-20 05:02:23.997

In this case, one would then need to cast the data-types to the same data-type to prevent implicit conversions from ruining the output, example:

SELECT COL1, COL2, CONVERT(VARCHAR(23), COL3, 121) AS COL3 FROM T1
UNION ALL
SELECT COL1, COL2, CAST(COL3 AS VARCHAR(10)) FROM T2
GO

COL1        COL2                   COL3
———– ———-             ———————–
1           ABC                    2007-01-21 18:39:23.990
2           XYZ                    2007-01-22 18:39:23.990
1           ABC                    123.120
2           2007-01-21 18:39:23.990     12345.210
From a performance perspective, UNION ALL is better than UNION since UNION forces a big sort and de-duplication i.e. a removal of the duplicate values.  And many times, the usage of UNION is not even needed and is used by programmers because they do not understand the difference between these two operators.

5 Responses to “UNION vs UNION ALL”

  1. […] just like the UNION/UNION ALL post that we did about implicit data-type conversions leading to issues, implicit data type conversions […]

  2. […] things you need to keep in mind when you are using UNION/ UNION ALL, please refer to our previous blog post. Now let us see how we can transform rows into columns. This gets bit trickier than the previous […]

  3. […] statement. Now, in the report, we needed to get these two together and as you know, if one uses the UNION or UNION ALL clause between two statements, one can use only one ORDER BY clause which applies to the whole set. So, it […]

  4. anxiety test nhs

    UNION vs UNION ALL « Systems Engineering and RDBMS

  5. buy youtube views

    UNION vs UNION ALL « Systems Engineering and RDBMS

Sorry, the comment form is closed at this time.

 
<span>%d</span> bloggers like this: