Set-Operation-&-Where-Subquery

  • Set Operations
    • Union
    • Intersection
    • Set difference
  • Subquery
    • IN subquery
    • NOT IN subquery

Set

Tables are treated as sets in relational databases.

Let A and B be two sets.

The union of and is the set .
The intersection of and is the set .
The set difference of and is the set =.

For example,


.

UNION

The two tables of the union must be compatible: corresponding columns must be of the same type.

TableA UNION TableB;
  • MySQL is very robust. It allows union between different types.

For example,

(SELECT actor_id FROM actor) UNION (SELECT first_name FROM actor)
However, this is NOT the reason for a union without type checking.

INTERSECTION

TableA INTERSECT TableB;

(SELECT columns FROM tables1 WHERE P1) 
INTERSECT 
(SELECT columns FROM tables2 WHERE P2)

The corresponding IN subquery is 

SELECT columns FROM tables1 
WHERE P1 AND columns IN (
    SELECT columns FROM tables2 WHERE P2
    )
  • Find the id of the English films which are played by Tim Hackman.
(SELECT film_id
FROM film_actor JOIN actor USING(actor_id) 
WHERE first_name='Tim' AND last_name='Hackman')
INTERSECT
(SELECT film_id 
FROM film JOIN language USING(language_id) 
WHERE name='English')
  • can implemented by IN
SELECT film_id
FROM film_actor JOIN actor USING(actor_id) 
WHERE first_name='Tim' 
  AND last_name='Hackman'
  AND film_id IN(
    SELECT film_id 
    FROM film JOIN language USING(language_id) 
    WHERE name='English')
  • It can also be implemented by using SOME.
    If an element is in a set, then the element is equal to some element in the set.
    SELECT film_id
    FROM film JOIN language USING(language_id)
    WHERE name = 'English' AND
      film_id = SOME(
        SELECT film_id
        FROM film_actor JOIN actor USING(actor_id)
        WHERE first_name='Tim' AND last_name='Hackman'
      )

Set Difference

For set difference use the keyword EXCEPT.

TableA except TableB

(SELECT columns FROM tables1 WHERE P1)
EXCEPT 
(SELECT columns FROM tables2 WHERE P2)

Alternatively we can use the NOT IN subquery.

SELECT columns FROM tables1 
WHERE P1 AND columns NOT IN (
SELECT columns FROM tables2 WHERE P2
)
  • Find the id of the films which are played by Tim Hackman but not in English.
(SELECT film_id 
FROM film_actor JOIN actor USING(actor_id) 
WHERE first_name="Tim" AND last_name="Hackman")
EXCEPT
(SELECT film_id
FROM film JOIN language USING(language_id)
WHERE name="English")
  • can implemented by NOT IN
SELECT film_id 
FROM film_actor JOIN actor USING(actor_id) 
WHERE first_name="Tim" 
AND last_name="Hackman"
AND film_id NOT IN (
  SELECT film_id
  FROM film JOIN language USING(language_id)
  WHERE name="English")
Table Of Contents
Set-Operation-&-Where-Subquery
Set
UNION
INTERSECTION
Set Difference