SQL – UNION

Prev     Next

• SQL UNION clause is used to combine the two tables together. There will be 2 separate SELECT SQL’s those are combined using UNION clause.
• UNION selects records from both select queries and stores in a temporary result table. And then, UNION returns only distinct records from this temporary result table and displays in the output.
• All records are returned from both tables if there is no single duplicate record between them.
• I.e. SQL UNION removes duplicate records between 2 tables and selects only unique records from them.
• In the SQL UNION clause, both SELECT statements should have the same number of columns and in the same order and in similar data type.

SQL Syntax for UNION:

SQL Syntax for UNION

SELECT column1, column2, etc FROM table1 UNION SELECT column1, column2, etc FROM table2;

Please consider following 2 tables with few records as given below.

Table1:

Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows

Example for how to use SQL UNION:

SQL query:

SELECT Student_ID, City FROM student1
UNION
SELECT Student_ID, City FROM student2;

Description:

In above SQL UNION, 2 SELECT SQL’s are executed, and then UNION verifies the result set of both SELECT queries. It removes all duplicate records from them and then returns only distinct records in the output.

RUN SQL

SQL query Output:

• Output of SQL statement – SELECT Student_ID, City FROM student1;

• Output of SQL statement – SELECT Student_ID, City FROM student2;

• Final Output using UNION: (Total records – 6)

Prev     Next