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 condition, 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 combinations 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 containing 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 combinations 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] ) 

However, each of the 96 rows contains the same amine. Table 6.1 is a subset 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 following SQL will accomplish this. 

---

As discussed in Chapter 3, a view is a subset of a table defined by a select statement. This is quite similar to the subselect statement discussed above. Such subselect statements are sometimes also called in-line views. Here we discuss the use of views as a persistent way to store subselect statements for use in SQL statements. 

This SQL statement can be expanded in many different ways to satisfy many different requirements. For example, an additional where clause in the subselect statements could limit selection of reactants by molecular weight, cost, availability, etc. The type of amine or acid chloride could also be selected by changing the SMARTS in the matches function. For example, aromatic amines could be selected by using matches (smiles, c[NHl] ). 

Table 6.2 shows the first few rows selected by this SQL statement. This is the result that was desired a total of 96 compounds consisting of a combination of 8 amines and 12 acids. These separate select statements are typically called subselect clauses of an SQL statement. They are enclosed in parentheses and named uniquely. They function as if they were tables themselves, but are actually subsets, or subselects of a table. There can be any number of subselects in an SQL statement and the subselect clause itself can be more complex than shown above. For example, one might also restrict the selected amines by molecular weight, vendor, or other criteria. 

---