Joins and Unions in DB2

Joins and Unions in db2

Unions and Joins, both are used to retrieve the data from multiple tables but are served for different purposes.

JOIN –
Joins typically used where the data required to select are spread across multiple tables that are dependent on each other.

For Example EMPLOYEE and DEPT are two tables.Here Employee details needs to be displayed along with his/her dept Name & Its Head.

Here EMPLOYEE table has 4 columns Emp NO, Name, designation & Department ID.

Employee Table:

DEPT table has Dept ID and Dept Name, Number of employees and Dept Head.

Department Table:

So to get the above employee Name and his Department name then both tables should be Joined on Dept ID.

SELECT EMP_NAME.EMPLOYEE, Dept_Name from EMPLOYEE, DEPT
WHERE DEPTID.EMPLOEE = DEPTID.DEPT ;

Basically Join forms ROWs by selecting columns from different tables, in the above example it takes Emp Name from Employee table and Department name from DEPT table and forms a resultant table.

Union

UNION is used to combine the results data from multiple tables/SQL queries. It combines the data from different SQls by adding rows.

Data that is to be combined is similar data but from different SQLs/tables.
For example, if we have two tables one for Transactions of India and the second one for international transactions.So my requirement is to get all the transactions and its details of any customer. Then UNION is the preferred option.

Key difference between the two is, Union combines the result set from different SQls where as JOIN produces result set by selecting columns from different tables. It is not mandatory that JOIN should always select columns from different tables, it can also select single column from table by using the other tables data in its WHERE predicate to filter the results.