Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 66

of rows being selected. Unless you intend to join every row of each table,
be sure each join clause contains an on condition.
There are other ways to construct an SQL statement that will select
exactly the same rows and columns from these tables. These are just
syntactical differences or stylistic differences. The methods shown here
is just one suggestion. Depending on which RDBMS is used, different
approaches may be more or less efficient.
6.7 Subselect Statements
In the SQL examples discussed previously, tables were joined with each
other using the on condition to correlate the appropriate rows and a final
where clause to restrict the selection of data. Without using the on condi-
tion, every row of one table would be joined with every row of the other,
resulting in more rows than desired. Sometimes, one wishes to join all rows
from one table with all rows from another to result in all possible combina-
tions of rows. Unless the tables are relatively small, this may still result in
more rows than desired. For example, in a table of nci.structures con-
taining only 250,000 structures, combining all rows with each other would
result in 62,500,000,000 rows! Even if a where clause is used to restrict the
number of selected rows, it is inefficient (and unnecessary) to produce com-
binations in this way.
For example, one may wish to combine amines and carboxylic acids
for consideration in a combinatorial chemistry experiment. The following
SQL would produce 96 rows.
Select amine.smiles As amines, acid.smiles As acids
From nci.structure amine, nci.structure acid
Where matches(amine.smiles, 'C[N!H0!R][C;D4]')
And matches(acid.smiles, 'CC(=O)[OH]')
Limit 96;
However, each of the 96 rows contains the same amine. Table 6.1 is a sub-
set of the rows resulting from the above SQL.
What might be desired instead is a test set of 8 amines and 12 acids
for a total of 96 rows. This can be accomplished if the amines are selected
separately from the acids, each in a select statement of their own. The fol-
lowing SQL will accomplish this.
Select amine.smiles As amines, acid.smiles As acids From
(Select smiles From nci.structure
Where matches(smiles, 'C[N!H0!R][C;D4]') Limit 8) amine,
(Select smiles From nci.structure
Where matches(smiles, 'CC(=O)[OH]') Limit 12) acid;