Some SQL-based databases include a wider range of operations that can be performed on two query result sets. These are MINUS or EXCEPT, in which the result contains only the rows from the first result that do not appear in the second, and INTERSECT, in which the result contains only rows that appear in both results.
MySQL does not understand these keywords, but there are ways to perform these operations using other techniques.
Performing a MINUS
The MINUS operator usually is placed between two queries. The result of the first query is returned, excluding any identical rows that are produced by the second query.
Using a similar example to the others in this chapter, you could use MINUS to find only first_name values from customer_contacts that are not also used as last_name values. The following query would return all the first_name values except for Franklin:
SELECT first_name FROM customer_contacts MINUS SELECT last_name FROM customer_contacts;
To reproduce this functionality in MySQL, you can rewrite the query as a subselect using the NOT IN operator, as follows:
SELECT first_name FROM customer_contacts WHERE first_name NOT IN ( SELECT last_name FROM customer_contacts );
An alternative, although not as readable, way to execute the same query uses a LEFT JOIN, as follows:
SELECT c1.first_name FROM customer_contacts c1 LEFT JOIN customer_contacts c2 ON c1.first_name = c2.last_name WHERE c2.last_name IS NULL;
Using a LEFT JOIN causes every row from c1 to be returned even if there is no matching row in c2. When there is no matching row, the values returned for c2 are all NULL. By filtering on the NULL values, you can exclude values for which the join was successful.
Performing an INTERSECT
The INTERSECT operator usually is placed between two queries; any data row that appears in the result of each individual query will appear in the overall result.
Using a similar example to the others in this chapter, you could use an INTERSECT to find only first_name values from customer_contacts that are also used as last_name values. The following query would find only the name Franklin from the sample tables:
SELECT first_name FROM customer_contacts INTERSECT SELECT last_name FROM customer_contacts;
An INTERSECT can be performed as a join in which you compare values from two different tables that are not otherwise related. For example, you could rewrite this query as follows:
SELECT c1.first_name FROM customer_contacts c1 JOIN customer_contacts c2 ON c1.first_name = c2.last_name;
No relationship exists between first_name and last_name, but you can use these values as the join condition to simulate an INTERSECT.