Page 209 - Informatics_Practices_Fliipbook_Class12
P. 209
UNION, INTERSECTION AND DIFFERENCE IN SQL
You may combine the results returned by two different SELECT clauses using union, intersection and
set-difference operators. The tables involved in these operations must be union compatible. Let us
consider the problem discussed before. A company wishes to give bonuses to all employees working in
the Administration department or working for more than 20 hours on any project. We can answer this
query by writing two queries and taking the union of the results obtained on executing them. The first
query finds the ID of employees working in the Administration department, and the second query finds
the ID of employees working for more than 20 hours on any project. Thus, we may use the following
SQL statement to answer the original query:
(SELECT E.ID
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE E.Dept_No = D.Dept_No AND D.Dept_Name = 'Administration')
UNION
(SELECT DISTINCT Emp_Id
FROM WORKS_ON
WHERE Hours > 20);
Result after executing the above statement is shown in Table 5.12.
ID
10001
10003
10008
10010
10006
Table 5.12: UNION for displaying the employees who are either working in the Administration department or
work for more than 20 hours on any project.
Similarly, you may use operators INTERSECT and EXCEPT for intersection and set difference,
respectively, depending upon the problem statement.
a. Examples
Let us now practice some SQL queries that retrieve data from multiple tables.
EMPLOYEE TABLE
Pin_ Dept_
ID FName LName Gender Address City DOB Salary
Code No
10001 Raj Reddy M West Godavari Andhra 534197 1980-06-13 100000 2
Pradesh
10002 Dhiraj Bora M Dispur, Kamrup, Guwahati 781005 1975-09-30 85000 1
Assam
10003 Muskan Taneja F 8/33, Geeta Delhi 110031 1990-01-25 100000 2
Colony
SQL: Working with Two Tables 195

