Queries with intersect and minus in MySQL | [...

Queries with intersect and minus in MySQL

• intersect
• minus
• mysql
• set operator
• sql

MySQL doesn't support the set operator INTERSECT. If you need a working replacment for it, instead of computing slowly in a client application, you can express it in SQL:

The simplest case when you need the intersection of two queries that are performed on two different tables but on a common subset of their columns.
SELECT a.name, a.age
FROM a INNER JOIN b
USING (name, age)

If the columns differ in their names, it is still not a big problem.
SELECT a.name, a.age
FROM a  INNER JOIN b
ON a.name=b.nickname AND a.age=b.age

But what if one of the tables the query is performed on isn't actually a table, but a subquery? In this case just
SELECT alias FROM
(SELECT concat(username,'@',domain) AS alias FROM maildb.users) AS tmp JOIN aliases
USING (alias)

Another aproach would be to create a temporary or permanent view from the subquery, and perform the query doing the intersection as in the first two cases.

The last intersecting query is for the problem when you have to decide, wheter the intersection of two or more (sub)qieries empty is.
SELECT * FROM (
          SELECT DISTINCT concat(username,'@',domain) AS alias FROM email_users
                    UNION ALL
          SELECT DISTINCT alias FROM email_aliases
                    UNION ALL
          SELECT DISTINCT orgiginal_address AS alias FROM email_forwards
) AS tmp
GROUP BY tmp.alias HAVING COUNT(*)>1
In this latter query we get the e-mail addresses wich appaer in at least two of the tables of users, aliases and forwards. If the three tables are pairwise disjoint, we get the empty set as result.

MySQL also lacks of the operator MINUS. A good replacemnet you can use is this:
SELECT DISTINCT name,id
FROM people
WHERE (name,id) NOT IN
(SELECT name, id FROM aliens);

A more nice-looking SQL statement would use a LEFT JOIN like this:
SELECT DISTINCT people.id, people.name
FROM people LEFT JOIN aliens USING (name,id)
WHERE aliens.id IS NULL

Of course this solutions does not perfrom as good as a native support in MySQL would be, but they are still better in performance then doing an intersection in a Perl/PHP/Ruby script on two result sets.