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

Finally, data about the compounds for each substance is also needed.
The compound _ id is contained in the substance _ compound table.
The SQL is extended to become
Select
n.sid, n.activity_outcome, n."log_gi50_M", n.log_gi50_ugml,
s.ext_datasource_name, s.ext_datasource_regid,
sc.compound_id, sc.compound_type
From
nci_h23 n
Join substance s On s.substance_id = n.sid
Join substance_compound sc On sc.substance_id = s.substance_id
Where activity_outcome = 2
Order By sid;
The data about the compounds is contained in the compounds table that
is indexed by compound _ id . The SQL now becomes
Select
n.sid, n.activity_outcome, n."log_gi50_M", n.log_gi50_ugml,
s.ext_datasource_name, s.ext_datasource_regid,
sc.compound_id, sc.compound_type,
c.openeye_can_smiles
From
nci_h23 n
Join substance s On s.substance_id = n.sid
Join substance_compound sc On sc.substance_id = s.substance_id
Join compound c On c.cid = sc.compound_id
Where activity_outcome = 2
Order By sid;
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 rela-
tions among the tables. Finally, any additions to the where clause can be
added as desired.
The above approach works well when tables are joined using keys
intended to relate tables to each other, as compound _ id and sub-
stance _ id here. The use of the join ... on construct is typical of
tables joined using keys. Sometimes tables are intentionally joined with-
out an on condition. When this is done, every row of each table is com-
bined with every row of the other, potentially producing a large number