---

Techniques to find the intersection and difference of 2 sets

---

• Computing the Intersection
  • Fact:

    Some Dayabase Systems (e.g. MySQL version 5.5.24) do not support the INTERSECT operation.

    ---

  • How to compute the intersection of 2 sets:

    You can compute the intersection of two sets as follows: x IN ( set1 INTERSECT set2 ) is the same as: (x IN set1) AND (x IN set2 )

    ---

    ---

  • Example:

    Find fname and lname of employees who (1) works in department 5 and (2) has a dependent

  • Solution:

    SELECT fname, lname FROM employee WHERE ssn ∈ { ssn of employee who works in dept 5 } AND ssn ∈ { ssn of employee who has a dependent } Written on using SQL syntax: SELECT ssn, fname, lname, dno FROM employee WHERE ssn IN (SELECT ssn FROM employee WHERE dno = 5) AND ssn IN (SELECT essn FROM dependent) Result: +-----------+--------+-------+-----+ | ssn | fname | lname | dno | +-----------+--------+-------+-----+ | 123456789 | John | Smith | 5 | | 333445555 | Frankl | Wong | 5 | +-----------+--------+-------+-----+

  ---

  ---

  ---

  ---

  ---

  ---

• Computing the Difference of 2 sets
  • Fact:

    SQL does not provide a set diffrence operation

    ---

  • How to compute the difference between 2 sets:

    You can use the following technique to compute the (set-) difference between two sets: x IN ( set1 - set2 ) is the same as: (x IN set1) AND (x NOT IN set2 )

    ---

  • Example:

    Find fname and lname of employees who (1) works in department 5 and (2) does not have any dependent

    Solution using set difference:

    SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E ∈ ( { employees of department 5 } - { employee WITH dependent } )

    ---

    ---

  • SQL solution:

    We re-write the set-difference: SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E ∈ { employee of department 5 } AND E ∉ { employee WITH dependent } Or: SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E.ssn ∈ { ssn of employee of department 5 } AND E.ssn ∉ { ssn of employee WITH dependent } Replace ∈ and ∉ with IN and NOT IN: SELECT ssn FROM employee E /* We test 1 employee at a time, call him "E" */ WHERE E.ssn IN { ssn of employee of department 5 } AND E.ssn NOT IN { ssn of employee WITH dependent } Use sub-query to find the 2 sets: SELECT ssn, fname, lname, dno FROM employee E WHERE E.ssn IN (SELECT ssn FROM employee WHERE dno = 5) AND E.ssn NOT IN (SELECT essn FROM dependent) Result: +-----------+--------+---------+-----+ | ssn | fname | lname | dno | +-----------+--------+---------+-----+ | 666884444 | Ramesh | Narayan | 5 | | 453453453 | Joyce | English | 5 | +-----------+--------+---------+-----+