UNION

UNION

The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION must have the same number of fields in the result sets with similar data types.

The basic syntax is as follows:

SELECT Column1, column2, ... column_n  FROM tables WHERE conditions  UNION  SELECT Column1, column2, ... column_n  FROM tables  WHERE conditions;

Parameters or Arguments

aggregate_function

A function such as SUM, COUNT, MIN, MAX, or AVG functions.

column1, column2, ... column_n

The columns that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

condition

The condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Employee Emp_id Name Surname Salary Dept_id 1 Rajesh Khanna 45000 2 2 Sneha Gupta 55000 4 3 Sandeep Nehte 95000 1 4 Kirti Patil 25000 3 5 Nilesh Jadhav 45000 3 6 Dipesh Das 35000 5 7 Kailash Rane 62000 1 8 Kavita Deshpande 48000 3 9 Sanjay Datt 50000 3 10 Shruti Hasan 78000 1

Employee Dept_id Dept_Name Loc_id 1 IT 101 2 EC 108 3 SUPPORT 103 4 MANAGEMENT 104 5 CIVIL 105

Syntax For MySQL:

SELECT Dept_id  FROM Employee UNION  SELECT Dept_id  FROM Department

After Execute: 

Dept_id
2
4
1
3
8
0
5

UNION ALL

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

The basic syntax is as follows:

SELECT Column1, column2, ... column_n  FROM tables WHERE conditions  UNION ALL SELECT Column1, column2, ... column_n  FROM tables  WHERE conditions;

SQL syntax:

SELECT Dept_id  FROM Employee WHERE Salary > 60000  UNION ALL SELECT Dept_id  FROM Department  WHERE Dept_Name ='Sandeep';

After Execute: 

Dept_id

1

1

1