MySQL Cookbook

Converting Subselects to Join Operations

Problem

You want to use a query that involves a subselect, but MySQL will not support subselects until Version 4.1.

Solution

In many cases, you can rewrite a subselect as a join. Or you can write a program that simulates the subselect. Or you can even make mysql generate SQL statements that simulate it.

Discussion

Assume you have two tables, t1 and t2 that have the following contents:

mysql> SELECT col1 FROM t1;
+------+
| col1 |
+------+
| a    |
| b    |
| c    |
+------+
mysql> SELECT col2 FROM t2;
+------+
| col2 |
+------+
| b    |
| c    |
| d    |
+------+

Now suppose that you want to find values in t1 that are also present in t2, or values in t1 that are not present in t2. These kinds of questions sometimes are answered using subselect queries that nest one SELECT inside another, but MySQL won’t have subselects until Version 4.1. This section shows how to work around that problem.

The following query shows an IN( ) subselect that produces the rows in table t1 having col1 values that match col2 values in table t2:

SELECT col1 FROM t1 WHERE col1 IN (SELECT col2 FROM t2);

That’s essentially just a “find matching rows” query, and it can be rewritten as a simple join like this:

mysql> SELECT t1.col1 FROM t1, t2 WHERE t1.col1 = t2.col2;
+------+
| col1 |
+------+
| b    |
| c    |
+------+

The converse question (rows in t1 that have no match in t2) can be answered using a NOT IN( ) subselect:

SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);

That’s a “find ...