CS457 Syllabus & Progress

### Set operations (UNION, INTERSECT, MINUS, DIVISION) in SQL

• Set operators in SQL

• Recall that Relational Algrbra has the following set operators:

 ∪ (set union) ∩ (set intersection) − (set difference) × (Cartesian product)

• Implementation and availability notes:

 ∪: Most SQL implementations provide the ∪ (UNION) operation ---- because it's very easy (O(N) running time) to merge 2 result sets ∩: Only a few SQL implementations provide the ∩ (INTERSECT) operation ---- because it's hard (O(N lg(N)) running time ) to intersect 2 sets −: I have no yet seen an SQL implementation that providse the − (MINUS) operation ---- (O(N lg(N)) running time ) to compute the difference of 2 sets ×: The Cartesian Product is built-in to the SELECT command....

• Fear NOT:

 The results of the INTERSECT and MINUS (set difference) operations can be computed through other ways !!!

• UNION operator

• The UNION operator can be used to combine the outputs of two or more SQL queries :

• Example:

 ``` select name from dependent name ---------- Alice Theodore Joy Abner Micheal Alice Elizabeth select name from dependent union select dname from department name --------------- Alice Theodore Joy Abner Micheal Elizabeth Research Administration Headquarters ```

• Example:

 Find the name of projects that are worked on by 'Smith' or 'Borg'

Solution:

 ``` (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Smith') UNION (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Borg'); ```

• NOTE: MySQL do not know squat about the meaning of each attribute.

MySQL (version 5.5.24) will even accept the following UNION:

 ``` (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Smith') UNION (SELECT pnumber FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Borg'); ```

Result:

 ``` +----------+ | pname | +----------+ | ProductX | | ProductY | | 20 | <----- How is 20 a pname ???? +----------+ ```

• INTERSECT operator

• The INTERSECT operator is similar to UNION and computes the intersection of the sets.

• Example:

 Find the name of projects that are worked on by 'Wong' AND worked on by 'Borg'

Solution:

 ``` (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Wong') INTERSECT (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Borg'); ```

Note:

 INTERSECT is not available in MySQL (version 5.5.24)

• The MINUS operator

• The MINUS operator is similar to UNION and computes the difference of the sets.

• Example:

 Find the name of projects that are worked on by 'Wong' but not worked on by 'Borg'

Solution:

 ``` (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Wong') MINUS (SELECT pname FROM project, works_on, employee WHERE pnumber = pno AND essn = ssn AND lname = 'Borg'); ```

Note:

 MINUS is not available in MySQL (version 5.5.24)

• Set division

• The set division operation is not part of the SQL standard.

• Therefore, SQL will never have the set division operation