SQL UNION Operator

The UNION operators are used to combine the result-set of two or more SELECT queries statements in SQL server. UNION has to perform distinct sort to remove dublicates, which makes it less slower than UNION ALL.

Syntax example of UNION

SELECT * FROM union_table_1
UNION
SELECT * FROM union_table_2;

• The UNION clause produces distinct values in the result set, to fetch the duplicate values, it means UNION Operator removes duplicate rows between the various SELECT statements.

Example With Tables

Combined tables [using UNION]

SELECT * FROM Student_Table_1
UNION
SELECT * FROM Student_Table_2;

Result Look Like

SQL UNION ALL Operator

The UNION ALL operators are also used to combine the result-set of two or more SELECT queries statements in SQL server. BUT UNION ALL, getting all the row including the dublicats rows.

• UNION ALL does not removies duplicate rows.
• UNION ALL must be used instead of just UNION.
• The UNION ALL operator little faster than UNION operator.

Combined tables [using UNION ALL]

SELECT * FROM Student_Table_1
UNION ALL
SELECT * FROM Student_Table_2;

Result Look Like

NOTE -

• Each SELECT statement within the UNION and UNION ALL must have the same number of fields (columns) in the result sets with similar data types.
• Must have the same expressions and aggregate function in each SELECT statement.

Shorting Result by a UNION or UNION ALL

ORDER BY clause should be used only on the last SELECT statement in the UNION or UNION ALL query.

For Example

SELECT * FROM Student_Table_1
UNION
SELECT * FROM Student_Table_2
ORDER BY StudentName;

Result Look Like

Interview Question

What are difference between UNION and JOIN?

ANSWER: There are two basic diffrence between UNION and JOIN in SQL server:

• UNION Combines ROWS from 2 or more tables.
• But JOIN Combines COLUMNS from 2 or more tables.