Join clause

The careful use of the Boolean operations and, or, and not along with parentheses will produce the desired set of rows from any table. When data are in separate tables that are related to one another, this approach also works well when the two tables are joined together using the SQL Join clause. 

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. 

Case Use a semicolon to separate independent clauses joined without a conjunction. 

Rule 2 If two clauses that could be separate sentences are joined by a linking word, such as and, hut, or, so, or yet, put a comma before the linking word. 

Second Case Use a semicolon to separate independent clauses that contain commas, even if the clauses are joined by a conjunction. 

Use the proper subordinating conjunctions. (Conjunctions join parts of a sentence subordinating conjunctions join subordinate clauses to the main sentence.) While and since have strong connotations of time. Do not use them where you mean although , because , or whereas . 

Use a semicolon between independent clauses joined by conjunctive adverbs or transitional phrases such as that is , however , therefore , hence , indeed , accordingly , besides , and thus . 

Dr. Claus Jordan died in June 21, 1995, four days before his 70. birthday. He didn t have the privilege to finish his efforts and to see Gottfried Weise, whom he always considered to be a innocent man, back in freedom. But at the very least, he joined the ranks of those being prosecuted for their contributions and work for justice In March 1995, the Tubingen judge Stein started judicial... 

Claus in 1856 preferred not to indicate exactly how the molecule was joined together. Thus, in order to show connecting influence, he used arched lines (P), as follows ... 

Use a comma to separate two independent clauses joined by a coordinating conjunction. To remember the coordinating conjunctions, think of the acronym FANBOYS for, and, nor, but, or, yet, and so. Always use one of the FANBOYS and a comma when you join two independent clauses. 

Please note that however can never be used as a coordinating conjunction joining two independent clauses. Incorrect use of however most often results in a comma splice. 

Notice that the Join keyword is not used, but rather just a comma separates the two tables being joined. The Join condition becomes a part of the where clause. The former syntax using the Join keyword will be used in examples throughout this book. 

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. 

Figure 6.3 shows the relationship between the pubhcem.nci h23 and pubchem.substance tables in the form of an entity-relationship diagram (ERD). The primary key substance.substance id and the foreign key nci h23.sid are indicated and imply their use in an On clause when these two tables are joined. 

Notice that a table alias n is used to refer to the table nci h23. This shorthand notation will make it easier to express complex SQL when more and more table joins are used. This statement is spread over several lines with indentation in order to locate and modify SQL clauses more easily. 

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. 

---