Structured Query Language (SQL) – Using the UNION / UNION ALL

Structured Query Language (SQL) – Using the UNION / UNION ALL

In this case, let’s consider the tables such that it will be easier to understand the intricacies of the Union.

Table DEPARTMENT contains the Dept_ID and the Dept_name.

Table EMPLOYEE contains the Emp_ID, emp_name, incentives, deductions  and contains the details for the permanent employees.

Table TEMP_EMPLOYEE contains the EMP_ID, emp_name and contains the details for temporary employees. The tables have been segregated to understand the UNION query.

SELECT d.dept_id, e.emp_name, e.incentives

FROM department d

INNER JOIN employee e

ON d.dept_id = e.dept_id

UNION

SELECT d.dept_ID, t.emp_name, 0

FROM department d

INNER JOIN temp_employee t

ON d.dept_ID = t.dept_ID

The query will display the list of permanent employees as well as temporary employees.

Note that there is no field called “incentive” in the table TEMP_EMPLOYEE, hence we have substituted the numeric value 0 in its place.

It is important to have the datatypes matching. See that d.dept_id from both tables match, the e.emp_name and t.emp_name datatypes match, and the e.incentives matches 0 (zero) from the second part of the query.

The UNION will display all the rows without duplicates, however the UNION ALL will show the duplicates too.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s