The Intersection and Difference Set Operators

Union, Intersection and Difference are three standard mathematical set operations. Whereas the UNION exists as a physical statement within the Informix DBMS, intersection and difference do not. However, the use of subqueries with the IN and NOT IN (EXISTS and NOT EXISTS, or = ANY and ! = ALL) conditions emulate intersection and difference, respectively.

For example:

SELECT c0001firstname, c0001surname
FROM t0001customer
WHERE c0001id IN (SELECT UNIQUE c0001id FROM t0002rental);

This emulates an intersection between the customer and rental tables.

SELECT c0001firstname, c0001surname
FROM t0001customer
WHERE c0001 NOT IN (SELECT UNIQUE c0001 FROM t0002rental);

This emulates a difference between the customer and rental tables.