UNION operator is used to combine SELECT statements of two or more of different tables in result set. UNION operator fetching DISTINCT rows from SELECT statements. By using UNION operator we get first SELECT statement column names in the result set. The columns used in all SELECT statements with UNION operator should be in the same order of DATA TYPE and the column names may vary. The number of columns in all SELECT statements with UNION operator also should be same. We can use n number of SELECT statements with UNION operator.

SELECT Expression1 [AS Alias_name],Expressio2,…
 FROM table1 [WHERE condition]
 UNION
SELECT Expression1, Expressio2,… FROM table2 [WHERE condition]; 

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Getting DISTINCT rows from existing tables by using UNION operator.

Requirement – Getting DISTINCT rows from existing employee_details and department_details tables by using UNION operator. The query was as follows -

SELECT emp_name, designation FROM employee_details
UNION 
SELECT dept_name, dept_location FROM department_details; 

By executing above query we can get DISTINCT rows from both employee_details and department_details tables by using UNION operator. The output was as follows –

Scenario – Getting single column with alias name by using UNION from existing tables.

Requirement – Getting single column values with alias name from both employee_details and department_details tables by using UNION operator. The query was as follows –

SELECT emp_name AS NAME FROM employee_details
UNION
SELECT dept_name FROM department_details; 

By executing above query we can get single column values like emp_name, dept_name from both employee_details and department_details tables by using UNION operator. The output was as follows –

Scenario – Getting all rows from existing tables by using UNION operator with WHERE condition.

Requirement – Getting all rows from existing employee_details and department_details tables by using UNION operator with WHERE condition. The query was as follows –

SELECT emp_name,designation FROM employee_details WHERE dept_id = 1000
UNION 
SELECT dept_name,dept_location FROM department_details WHERE dept_id = 1000; 

By executing above query we can get all rows from both employee_details and department_details tables by using UNION operator whose dept_id is 100.The output was as follows -