SAND CDBMS SQL Reference Guide
Joins

 

Previous Topic:
Outer Joins
Chapter Index
Next Topic:
Nested Joins

 

MATCH JOINS


The match join (also called the Springfield join) is an inner join that, for each row in one table, limits results to the first row that satisfies the join condition in the other table. In very simplified terms, the way SAND CDBMS executes a match join is as follows:

  1. Point to the first row in Table A.
  2. Start processing the rows in Table B from the beginning.
  3. If a row is found in Table B that satisfies the join condition for the current row in Table A, stop scanning Table B and add the matching rows to the result set.
  4. Point to the next row in Table A.
  5. Repeat steps 2 - 5 until there are no more rows in Table A.

The keywords LEFT and RIGHT are used to indicate which table's records are used to find the first match. Note that the following match join structures are equivalent:

A LEFT MATCH JOIN B ON join-condition

B RIGHT MATCH JOIN A ON join-condition


Like an OUTER JOIN, a MATCH JOIN requires that all of the join conditions appear in the ON clause. All predicates that appear in the WHERE clause are evaluated as a filter after the join, even if the predicates could have been added as another join condition.


LEFT MATCH JOIN

Left match joins return the first match (instead of all matches) on the join condition for each row in the table named before (that is, on the left of) the LEFT MATCH JOIN clause. Stated another way, each row in the left-side table will have at most one row from the right-side table that satisfies the join condition.

The following sample query creates a left match join between the Part and Partsupp tables:

SELECT part.pno, part.pname, partsupp.qty
  FROM part LEFT MATCH JOIN partsupp
  ON part.pno = partsupp.pno;

The query results set contains only the first matching row (if any) in the Partsupp table that satisfies the join condition for each row in the Part table. For example, if the Part table contains a record for a pno column value of “P9”, and the Partsupp table contains three records with matching pno values, only the first Partsupp record with the pno column value “P9” is match joined with the Part record.


RIGHT MATCH JOIN

Right match joins return the first match (instead of all matches) on the join condition for each row in the table named after (that is, on the right of) the RIGHT MATCH JOIN clause. Stated another way, each row in the right-side table will have at most one row from the left-side table that satisfies the join condition.

The following sample query creates a right match join between the Part and Partsupp tables:

SELECT part.pno, part.pname, partsupp.qty
  FROM part RIGHT MATCH JOIN partsupp
  ON part.pno = partsupp.pno;

The query results set contains only the first matching row (if any) in the Part table that satisfies the join condition for each row in the Partsupp table. For example, if the Partsupp table contains a record for a pno column value of “P9”, and the Part table contains three records with matching pno values, only the first Part record with the pno column value “P9” is match joined with the Partsupp record.

 

Previous Topic:
Outer Joins
Chapter Index
Next Topic:
Nested Joins