Structured Query Language (SQL) - Design and Use of Relational Databases in Chemistry - page 29

creates a view called test _ set , which is a subset of the table named
literature _ data . The test_set will only contain rows with octanol-
water partition coefficients reported by Hansch in 1985 and having a tem-
perature reported. The test _ set view can be used as if it were a table
in other Select statements. It is not possible to update or delete rows
from a view. Several examples of views are given in later chapters.
3.9 Unions, Intersections, and Differences
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:
Select logp From logp Where temp = 25 And
ref Like '%Hansch%' Or ref Like '%Yalkowsky%';
produces a different set of rows than the following:
Select logp From logp Where temp = 25 And
(ref Like '%Hansch%' Or ref Like '%Yalkowsky%');
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.
When data are selected from tables that are not related to one another,
a different approach is used. The SQL operators union , intersect , and
exclude allow set operations on tables or the sets of rows resulting from
a select statement. For example:
Select logp,temp from logp Where ref Ilike '%Hansch%' And temp = 25
Union
Select logp,temp From merck Where temp Is Not Null;
produces a set of rows from two unrelated tables, logp and merck. It is nec-
essary that the number and data types of the columns from each select
statement be identical. Any number of Select statements may be com-
bined using this method. The union , intersect , and except operations
can be mixed in any order, using parentheses as necessary to effect the cor-
rect overall Boolean operation. It is possible to use this method to combine
results selected from the same table or from related tables. In those cases,
it is possible to craft two different SQL statements—one using intersect ,