SQL UNION / UNION ALL

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Introduction

UNION keyword in SQL is used to combine to SELECT statement results with out any duplicate. In order to use UNION and combine results both SELECT statement should have same number of column with same data type in same order, but the length of column can be different.

Syntax

  • SELECT column_1 [, column_2 ] FROM table_1 [, table_2 ] [WHERE condition]
    UNION | UNION ALL
    SELECT column_1 [, column_2 ] FROM table_1 [, table_2 ] [WHERE condition]

Remarks

UNION and UNION ALL clauses combine the result-set of two or more identically structured SELECT statements into a single result / table.

Both the column count and column types for each query have to match in order for a UNION / UNION ALL to work.

The difference between a UNION and a UNION ALL query is that the UNION clause will remove any duplicate rows in the result where the UNION ALL will not.

This distinct removal of records can significantly slow queries even if there are no distinct rows to be removed because of this if you know there wont be any duplicates (or don't care) always default to UNION ALL for a more optimised query.



Got any SQL Question?