Relational Operators
-
JOIN
You can join two or more tables based on the values of fields which are common to the specified tables, excluding those records with non-matching values for the fields specified.
SELECT <field_list>
FROM <table_1>, <table_2> [, <table_n> ...]
WHERE <field_comparison> [AND < field_comparison> ...]
In the following example, the HEADERS and SURVEYS tables are joined based on values in the BHID field:
SELECT *
FROM headers H, surveys S
WHERE (H.BHID = S.BHID)
In the following example more than two tables are joined - the HEADERS table is joined to SURVEYS, and then the SURVEYS table is joined to ASSAYS. In this case, the joining table SURVEYS acts as a source table for the joining table ASSAYS.
SELECT *
FROM header H, surveys S, assays A
WHERE (H.BHID = S.BHID) AND (S.BHID = A.BHID)
Field Concatenation
Values from multiple join fields may be concatenated to produce a single value that can be used in comparison with the value of a single field from the source table.
In the following example, the PROJECT_NO and SUBPROJECT_NO fields in JOIN_TABLE are concatenated and compared with the values in the single field PROJECT_ID in SOURCE_TABLE:
SELECT *
FROM source_table S, join_table J
WHERE (S.PROJECT_ID = J.PROJECT_NO || J.SUBPROJECT_NO)
-
INNER
Using the optional INNER keyword and the JOIN keyword, you can join two or more tables based on the values of fields which are common to the specified tables, excluding those records with non-matching values for the fields specified.
SELECT <field_list>
FROM <table_1>
[INNER] JOIN <table_2>
ON <field_comparison>
[[INNER] JOIN <table_n>
ON <field_comprison> ...]
For example:
SELECT *
FROM headers H
INNER JOIN surveys S
ON (H.BHID = S.BHID)
More than one table may be joined with an INNER JOIN. In the following example, the optional INNER keyword is left out:
SELECT *
FROM headers H
JOIN surveys S
ON (H.BHID = S.BHID)
JOIN assays A
ON (S.BHID = A.BHID)
-
OUTER
Using the OUTER keyword and the JOIN keyword, you can join two or more tables based on the values of fields which are common between the specified tables.
The difference between an INNER and an OUTER join - In an OUTER join the rows from the source table that do not have a match in the joining table are included in the result set. Those fields from the joining table which are not matched with values from the source table are given NULL values.
SELECT <field_list>
FROM <table_1>
LEFT | RIGHT | FULL [OUTER] JOIN <table_2>
ON <field_comparison>
[LEFT | RIGHT | FULL [OUTER] JOIN <table_n>
ON <field_comparison> ...]
In the example below, the HEADER and SURVEYS tables are joined based on values in the BHID column. NULL values are written to the fields from the SURVEYS table for any rows from HEADERS that do not have a matching BHID value in SURVEYS.
This may be useful when you want to determine the success or otherwise of a join operation. In the example below, the LEFT modifier causes all rows from the table on the left of the OUTER JOIN operator to be included in the result set, with or without matches in the table to the right.
Similarly, the use of a RIGHT modifier causes all rows from the table on the right of the OUTER JOIN operator to be included in the result set, with or without matches.
The FULL modifier causes all rows from all of the tables specified in the FROM clause to be included in the result set, with or without matches.
SELECT *
FROM headers H
LEFT OUTER JOIN surveys S
ON (H.BHID = S.BHID)
An example using the FULL modifier:
SELECT *
FROM headers H
FULL OUTER JOIN surveys S
ON (H.BHID = S.BHID)
FULL OUTER JOIN assays A
ON (S.BHID = A.BHID)
-
UNION
Use UNION to append the rows of one table to the end of another table. The SELECT statement for the source and joining tables must include the same number of fields.
The table structures of the tables you are combining don't have to be the exactly the same. However, the types of the fields included in the SELECT statements must match.
SELECT <field_1> [, <field_2>, ... <field_n>]
FROM <table_1>
UNION [ALL]
SELECT <field_1> [, <field_2>, ... <field_n>]
FROM <table_2>
For example:
SELECT ten_ID, company
FROM tenements
UNION
SELECT ten_ID, company
FROM old_tenements
To get around problems when there are differences in field types, you may be able to use the CAST function to convert the fields to a compatible type. For example:
SELECT T.ten_ID, CAST(T.expiry_date AS TIMESTAMP)
FROM Tenements T
UNION ALL
SELECT O.Ten_id, O.exp_timestamp
FROM Old_tenements O
Matching names are not required when retrieving data for fields across the multiple tables. Differences in field names between the multiple source tables are automatically handled. The names used in the first SELECT statement will be used by default in the result set.
In a UNION join, non-distinct rows are aggregated into single rows by default. To retain non-distinct rows use the ALL keyword.
If you want to join two tables with UNION where one table does not have a field which is included by another, you can replace the missing field with a literal value or an expression in the SELECT statement.
For example, if there is no field in the join table corresponding to a DESCRIPTION field in the source table, then an expression can be used to provide a value for a pseudo DESCRIPTION field in the join table:
SELECT S.ID, S.Description
FROM source_table S
UNION ALL
SELECT J.ID, CAST("" AS CHAR(50))
FROM join_table J