---

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 input sets ∩: Only a few SQL implementations provide the ∩ (INTERSECT) operation Because it's costly (O(N lg(N)) running time ) to intersect 2 sets −: Very few SQL implementation that providse the − (MINUS) operation The − operation is not used much and it's costly (O(N lg(N)) running time ) to compute the difference of 2 sets ×: The Cartesian Product (×) is built-in (implicit) to the FROM-clause of the SELECT command....

    ---

  • Note:

    The INTERSECT and MINUS (set difference) operations can be computed through other ways !!! We will discuss this later (because we need use a sub-query that has not been discussed yet)

  ---

  ---

  ---

  ---

• UNION operator

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

  • Example:

    SSN of employees who works on project 1: select essn from works_on where pno=1; +-----------+ | essn | +-----------+ | 123456789 | | 453453453 | +-----------+ SSN of employees who works on project 2: select essn from works_on where pno=2; +-----------+ | essn | +-----------+ | 123456789 | | 333445555 | | 453453453 | +-----------+ Union of these 2 sets is: (select essn from works_on where pno=1) union (select essn from works_on where pno=2) +-----------+ | essn | +-----------+ | 123456789 | | 453453453 | | 333445555 | +-----------+

    ---

    ---

  • NOTE:

    The UNION operation is SQL will accept any 2 sets of different data type (As long as the number of attributes is same in the sets !!!) Example: this is correct (select hours from works_on where pno=1) union (select essn from works_on where pno=2) +-----------+ | hours | +-----------+ | 32.5 | | 20.0 | | 123456789 | | 333445555 | | 453453453 | +-----------+

  ---

  ---

  ---

  ---

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

  • Example:

    SSN of employees who works on project 1: select essn from works_on where pno=1; +-----------+ | essn | +-----------+ | 123456789 | | 453453453 | +-----------+ SSN of employees who works on project 2: select essn from works_on where pno=2; +-----------+ | essn | +-----------+ | 123456789 | | 333445555 | | 453453453 | +-----------+ Union of these 2 sets is: (select essn from works_on where pno=1) intersect (select essn from works_on where pno=2)

    Note:

    INTERSECT operation is in the SQL 99 standard but it is not available (not implemented) in MySQL (version 5.5.24)

  ---

  ---

  ---

  ---

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

  • Example:

    SSN of employees who works on project 1: select essn from works_on where pno=1; +-----------+ | essn | +-----------+ | 123456789 | | 453453453 | +-----------+ SSN of employees who works on project 2: select essn from works_on where pno=2; +-----------+ | essn | +-----------+ | 123456789 | | 333445555 | | 453453453 | +-----------+ Union of these 2 sets is: (select essn from works_on where pno=2) minus (select essn from works_on where pno=2)

    Note:

    MINUS operator is in the SQL 99 standard but it 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

  ---

  ---

  ---

  ---

• Query formulation in SQL that make use of the intersection, difference and division operations
  • There are query techniques in SQL that you can use (or learn to use) to do intersection, set difference and set division queries

    ---

  • We will discuss these next