Databases Reference
In-Depth Information
These two tables share two columns,
B
and
C
. Then we use the following code to
perform a regular join:
Table1:
LOAD * INLINE
[
A, B, C
1, 1, 1
2, 2, 2
3, 3, 3
];
JOIN
LOAD * INLINE
[
B, C, D
2, 2, 2
3, 3, 3
5, 5, 5
];
The result is the following table:
As you can see, the overlapping columns,
B
and
C
, have been merged into single
columns, and the fields
A
and
D
have been added from both tables. It is important
to note that, as the second table is being joined to the first the name of the table stays
Table1
. It is also important to note that the rows that could not be joined, the first
and the last, get null values for the missing values.
Make it explicit
When using just the bare
JOIN
statement, the join will be made to the
table loaded directly before the
JOIN
statement. If the table to join to
was loaded somewhere earlier in the script, that table can be joined
to by supplying its name in parentheses. In our example this would
be achieved by replacing
JOIN
with
JOIN (Table1)
. From the
perspective of keeping our code easy to understand, it is preferable to
always supply the name of the table to join to. While the load statement
for the table to join to may be directly above now, this may change in the
future. When that happens, the join is suddenly targeting another table.
