Other Compound Operations

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.

MariaDB 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 MariaDB, 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.

Optimizing MINUS

Both the techniques discussed in this section can produce very slow queries: The NOT IN operator cannot use an index, and a LEFT JOIN often has to process very large amounts of data in memory. If neither performs well enough for your situation, consider using a temporary table instead, as discussed later in this lesson.

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.