Union and UnionAll in SQL

Price: INR 1,299.00
Was: INR 1,799.00

Let’s understand the difference in Union and UnionAll in SQL. SQL supports few Set operations to be performed on table data.

Set operators combine the results of two component queries into a single result. Queries containing set operators are called compound queries.

 

In SQL, We have 4 types Set Operators –

  • UNION – All distinct rows selected by either query.

  • UNION ALL – All rows selected by either query, including all duplicates.

  • INTERSECT – All distinct rows selected by both queries.

  • MINUS – All distinct rows selected by the first query but not the second.

 

UNION Operator in SQL

The UNION operator is used to combine the result-set of two or more SELECT statements, However, it eliminates duplicate rows from its result set.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Union in SQL

 

SQL Syntax :

SQL> SELECT column_name  FROM table1
UNION
SELECT column_name FROM table2;

Example – Display the current and previous job details of all employees. Display each employee only once.

SQL > Select emp_id, Job_Id from Employees

           UNION

           Select emp_id, Job_Id from Job_history;

 

UNION ALL Operator in SQL

UNION  ALL is used to combine the results of two or more Select statements. However, it also shows the duplicate rows from its result set.

Union All in SQL

 

SQL Syntax :

SQL> SELECT column_name  FROM table1
UNION
SELECT column_name FROM table2;

Example – Display the current and previous departments of all employees.

SQL > Select emp_Id, Job_Id, Dept_Id from Employees

           UNION ALL
Select emp_Id, Job_Id, Dept_Id from Job_history;

 

INTERSECT Operator in SQL

Intersect operation is used to combine two SELECT statements, but it only returns the records which are common from both SELECT statements. In the case of Intersect, the number of columns and data type must be same. MySQL does not support INTERSECT operator.

Intersect in SQL

 

SQL Syntax :

SQL> SELECT column_name  FROM table1
INTERSECT
SELECT column_name FROM table2;

 

Example – Display the employee IDs and Job IDs of those  employees who currently have a job title that is the same as their job title when they were initially hired.

SQL > Select emp_Id, Job_Id, Dept_Id from Employees

           INTERSECT
Select emp_Id, Job_Id, Dept_Id from Job_history;

 

MINUS Operator in SQL

Minus operation combines the result of two Select statements and return only those result which belongs to the first set of result. MySQL does not support INTERSECT operator.

Minus in SQL

 

SQL Syntax :

SQL> SELECT column_name  FROM table1
MINUS
SELECT column_name FROM table2;

Example – Display the employee IDs and Job IDs of those employees who have not changed their jobs even once.

SQL > Select emp_Id, Job_Id, Dept_Id from Employees

           MINUS
Select emp_Id, Job_Id, Dept_Id from Job_history;

 

0 Comment

Leave a Reply