UNION, INTERSECTION and DIFFERENCE.
(Slide 73) The UNION operator in Relational Algebra takes two relations and returns a relation containing all the tuples appearing in both relations.
S { CITY } UNION P { CITY }
The UNION operator in MySQL would take the results of the two queries and return all distinct rows from both queries
SELECT CITY FROM S
UNION
SELECT CITY FROM P
-----------------------
(Slide 75) The INTERSECT operator in Relational Algebra takes two relations and returns a relation containing only tuples that appear in both relations.
S { CITY } INTERSECT P { CITY }
The INTERSECT operator in MySQL would need to take the results of two queries and return only the rows that appear in both result sets.
MySQL doesn’t support the INTERSECT
SELECT DISTINCT S.CITY FROM S
JOIN P ON S.CITY=P.CITY
-----------------------
(Slide 77) The MINUS operator in Relational Algebra takes two relations and returns the tuples that appear in the first relation that do not appear in the second relation.
S { CITY } MINUS P { CITY }
The MINUS operator in MySQL must take the distinct rows from the first query and return only the rows that do not appear in the result set of the second query.
MySQL doesn’t support the MINUS set operators.
We can rewrite these queries by using JOIN operator:
SELECT DISTINCT S.CITY FROM S
LEFT JOIN P ON S.CITY = P.CITY
WHERE P.CITY IS NULL
----------------------
Export the WPC database to your class server from http://ics321.com/assignments/WPC_SCHEMA.txt
This is a database of employees (EMPLOYEE), departments (DEPARTMENT), projects (PROJECT) and project assignments (ASSIGNMENT).
Notice Departments have Employees and Departments have Projects.
The database reflects this as an Employee having a Department attribute, and a Project having a Department attribute.
----------------------
Which departments have both employees and projects? (Intersection of Employee Departents and Project Departments)
SELECT DISTINCT E.DEPT FROM EMPLOYEE E
JOIN PROJECT P ON E.DEPT = P.DEPT
----------------------
Which departments have employees but no projects? (Employee Departments Minus Project Departments)
SELECT DISTINCT E.DEPT FROM EMPLOYEE E
LEFT JOIN PROJECT P ON E.DEPT = P.DEPT
WHERE P.DEPT IS NULL
----------------------
Take quiz http://ics321.com/quizzes/Quiz06.pdf