You want to use a query that involves a subselect, but MySQL will not support subselects until Version 4.1.
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.
Assume you have two tables,
t2 that have the following contents:
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
that are also present in
t2, or values in
t1 that are not present in
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
subselect that produces the rows in table
col1 values that match
col2 values in table
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:
SELECT t1.col1 FROM t1, t2 WHERE t1.col1 = t2.col2;+------+ | col1 | +------+ | b | | c | +------+
The converse question (rows in
t1 that have no
t2) can be answered using a
IN( ) subselect:
SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);
That’s a “find ...