Operation description

The operation that is needed as an alternative or complement to the join operation is match. The basis of the matching, just as in the join operation, is the operation of the relationship between the rows of tables (tuples). A distinctive feature of the matching goal is the result in the form of exactly one tuple value from the left table, the right table, or both the left and right tables according to a certain rule. In other words, the result of a match cannot be n:n, as is the case with a join operation. The result will be a strict and predefined ratio of 1:n, n:1 or 1:1.

This is necessary when the query result is based on financial data, in which case the result in the form of a Cartesian product threatens to multiply the amounts for individual rows. For example, when joined, the left table reflects payments for shipments, and the right table reflects batches. In this case, several shipments correspond to one shipment, and several payments can correspond to one shipment.

The matching task can be solved by selecting individual values through a subquery like:

Select a.*, (select top 1 field1 from TableB b where b.field3=a.field3 order by field4)
from TableA a

But, firstly, this design is extremely ineffective. And secondly, it allows you to extract only one field. And in a situation where it is necessary to add several (and quite a large number of fields), for each field it is necessary to build a separate query, which with each field reduces the efficiency of the overall query and increases its cumbersomeness.

Besides Join, another related operation for matching is Intersection. However, Intersection in the relational model frees itself from indicating the matching of each specific row in tables by grouping data (tuples). This is why the name “match” was chosen instead of the more traditional term intersection, because it is already occupied by an operation with a different meaning. Although calling this operation “intersection” would be more logical, since it is based on bijection as a 1:1 relation (one-to-one correspondence), injection as a 1:n relation, or surjection as an n:1 relation.

Similar to the operations related to a joining, the following operation types can be entered as matching types:

• inner matching,
• left, right and full matching,
• left, right and full anti-matching.

In this case, the operations of half-join and minus-half-join are the same for join, intersection, and matching. With the use of them, it is possible to determine whether the result belongs to the left or right set of values (table rows or tuples): the source table, indicated in the from construct, and the joined table, indicated in the join, match, intersect constructs. Semi-joins are visualized through an Euler-Venn diagram.

The analogy with Joining

Let’s return to the join operation to compare with the match operation. The rule for forming the result of joining table elements in the relational model is based on the Cartesian product – all elements that are included in the join on one side and on the other generate sequential link between all elements: all with all. If the join criterion does not give an exact match of 1:1, 1:n or n:1 for the left and right tables (but does give n:n), then for such tuples the Cartesian product between the left and right tables (relations) is applied.

In contrast, anti-joins or minus-joins (left and right residue) and joins based on the intersection of elements (left, right, full join) return a simple list of elements from one table that are not comparable (not matched) to elements of another table.

The question arises: is the Cartesian product the most “correct” solution, and inseparable from the join operation? The answer is no. The Cartesian product allows you to resolve uncertainty by returning a combinatorial result, but it is not always needed, desired, or correct.

Returning to the matching operation: the Cartesian product as we see it used in a join has a positive effect in terms of eliminating the uncertainty of matching table rows, but it also has the opposite negative effect in that the number of rows results in multiplication and sometimes duplication strings are difficult to control.

For example, the absence of duplication of rows is critical for queries with amounts: if the rows of the source table are duplicated, the amounts during aggregation will also increase, which will lead to negative consequences: a multiple increase in the summation results. Another example is the desire to store key values resulting from queries from the left, right, or both tables.

In this case, using the join operation is simply dangerous – it is advisable to replace it with another operation.

This is the purpose of the matching operation – to prevent duplicate rows. There are three modifications of this comparison:

1) The number of rows in the left table should not be multiplied. For external matching, the number of rows in the result must exactly match the number of rows in the left table (tuple).

2) The number of rows in the right table should not be multiplied. For outer matching, the number of rows in the result must exactly match the number of rows in the right table (tuple)

3) The number of rows in the result must correspond to the minimum of rows from the left and right tables (tuples). For this type of comparison, the concept of external comparison is not applicable – it is always inner, and cannot be different.

To implement the matching operation, the newly introduced Apply operator in MS SQL Server could potentially be used; it is designed to perform the matching more abstractly and more flexible compared to join, allowing you to break down 1) and 2) points discussed in the previous chapter. However, the question arises about implementing the matching operation through Apply: language constructs based on Apply become even more cumbersome and difficult to understand. In fact, the apply operation is similar to the Cartesian product operation in its original form:

select * from TableA, TableB where 

Remark: The operations of joining and matching are commutative (A join B = B join A, A right outer join B = B left outer join A). However, the joining must be evaluated from the perspective of a specific table – and this is usually the left table. This is to respect the default of the source table: sometimes it is important that the row in the source table matches the row or rows in the result. For example, the difference operation (Set difference) does not have a “left” and “right” prefix, which means that the tables in this operation are clearly defined.

An alternative to the Cartesian Product

In order to accurately compare the fields of the left and right tables, you need to choose some other method of resolving uncertainty than the Cartesian product. Uncertainty is expressed in the choice of one value from many comparisons between rows (tuples) of the table. The mapping of one row of one table to many rows in another table must be reduced to 1:1 through the selection of some single row.

One way to resolve uncertainty is to use the first row in the order of the fields according to a given criterion, that is, use sorting.

The SQL syntax for such a command may be similar to the syntax used in other statements. For example,

row_number() over (partition by field1 order by field2 asc)

Likewise, the matching command might look like:

select * from TableA a over (order by a.Field1) match TableB b over (order by b.Field2) on a.Field1=b.Field1

Taking into account what was described earlier, the match command can have the following modifications:

1) inner match, outer left match, outer right match, outer full match – determines the connection type

2) minus left match, minus right match, minus full match – completely coincides with anti-connections or minus connections, which is the complement of 1)

3) match left-single, match right-single, match full-single – determines the result of the relations 1:n, n:1 and 1:1, respectively.