Download Tigon SQL Users Manual

User Manual
AT&T Research
August, 2014
6.2.9.1. INNER_JOIN
An inner join will create a tuple only when it can match a pair of tuples from R and S.
Unmatched tuples are discarded (and hence inner join does filtering). An example query
which uses inner join is the following, which computes the delay between a syn and a
synack:
SELECT S.tb, S.sourceIP, S.destIP,S.source_port, S.dest_port
(R.timestamp-S.timestamp)
INNER_JOIN from tcp_syn_ack R, tcp_syn S
WHERE S.sourceIP=R.destIP and S.destIP=R.sourceIP and
S.source_port=R.dest_port and S.dest_port=R.source_port
AND
S.tb=R.tb and S.timestamp<=R.timestamp
6.2.9.2. OUTER_JOIN
An outer join will create an output tuple for every matched pair from R and S, and in
addition it will create an output tuple for each unmatched tuple from both R and S. The
reporting example above is a typical use of an outer join. If there are Inpackets but no
Outpackets (or vice versa) for a particular sourceIP, destIP pair, there still needs to be a
report about the Inpacket traffic (or Outpacket traffic). The query will generate a
meaningful output tuple in spite of the missing information, but there are some subtleties
to be aware of.
1. The SELECT clause will probably contain scalar expressions that reference both
R and S. When an output tuple is generated for an unmatched input tuple, there
will be missing information. If creating an output tuple for an unmatched R tuple,
use default values for fields from S (and vice versa). Integers become 0, floats
become 0.0, and strings become a empty ('').
2. There is an exception to the above rule. If there is a predicate in the WHERE
clause of the form R.fieldr=S.fields, the value R.fieldr is substituted for S.fields
when processing an unmatched tuple from R, and conversely for S. This is the
only condition under which GSQL will infer non-default values for the missing
field values. The motivation is that equality predicates on fields identify the join
key, and you are gathering a report about that key. The source, R or S, is
irrelevant, although you are forced to choose one when writing the SELECT
clause.
6.2.9.3. LEFT_OUTER_JOIN
The LEFT_OUTER_JOIN is like an outer join, except that only the matched pairs and
the unmatched tuples from R will generate an output tuple. Unmatched tuples from S are
discarded.
Page !40