Join clause select

Note two additional Join clauses, each with the appropriate On clause naming the columns that relate the tables being joined. The additional columns compound id, compound type, and openeye can smiles are from the compound table. No columns are actually selected from the substance compound table. That table is simply used to affect the many-to-many relationship between the substance and compound tables. 

Here you can see that each adverse event that was experienced has now been merged with each concomitant medication taken during that time. This is due to the SQL SELECT statement ON clause, which allows for complicated logic to be applied to the join. 

The results of a Select statement are in the form of a table. This can be a subset of a single table, or the result of joining several tables. The exact set of rows is chosen by using various Where clauses. The use of Boolean operation such as and, or, and not allows a sort of union (or), intersection (and), and difference (not). For example ... 

Notice the use of the Join keyword and the additional table name pubchem.substance in the From clause. This is necessary because data from this table is being selected. The additional columns selected are ext datasource name and substance.ext datasource regid in the Select clause. Any columns of interest in the substance table could be selected. Note that since there is a column named ext datasource id in both tables, it is necessary to specify that the column substance.ext datasource regid is desired. Finally, the clause On nci h23.sid= substance, substance id indicates that these columns are related to each other and must be used in the Join. 

Notice that each line in the select clause contains columns from only one table. Likewise, each line in the from clause contains one new table name with each table (after the first one) preceded by the join keyword. Each column uses a table name (a table alias) for brevity. Now that all the tables are properly joined, the columns selected can be arranged in any order desired. Some columns can also be removed from the select clause. The compound id and sample id may not be of interest in the final result, since these arbitrary values are used only to maintain relations among the tables. Finally, any additions to the where clause can be added as desired. 

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. 

More complex policies can be defined by using if-then-else statements to select the appropriate proceed clause. Also, multi-level policies are possible with resolvers of resolvers. The AIRIA compiler checks that, whatever the join point, a unique root resolver manages conflicts at this join point. A total execution order must be obtained from the tree of resolvers starting from the root. 

---