What is the major difference between UNION and UNION ALL?

UNION:

  • Selects only distinct records
  • First Selects all records and then takes distinct records from it
  • Slower than UNION ALL

UNION ALL:

  • Selects all values from all the result sets. Duplicates will not be eliminated
  • Selects all values from all the result sets. It will not eliminate duplicate records
  • Faster than UNION as it does not need to filter duplicate records

The following example demonstrates the basic difference between them.

Ex:

—————————————————————————————
— UNION Example: It returns one record.
—————————————————————————————
SELECT ‘SQLVersity is your SQL University’ AS [Union_Test]
UNION
SELECT ‘SQLVersity is your SQL University’
UNION
SELECT ‘SQLVersity is your SQL University’
—————————————————————————————
— UNION ALL Example: It returns three records.
—————————————————————————————
SELECT ‘SQLVersity is your SQL University’ AS [Union_All_Test]
UNION ALL
SELECT ‘SQLVersity is your SQL University’
UNION ALL
SELECT ‘SQLVersity is your SQL University’
—————————————————————————————

Result set:

Union vs Union All

Note:

If we know that all the records returned by the Union are Unique, use UNION ALL instead of UNION as it gives results faster than UNION.

Leave a Reply

Your email address will not be published. Required fields are marked *