5.4.2. Join (High-Speed clip0090 action)

<< Click to Display Table of Contents >>

Navigation:  5. Detailed description of the Actions > 5.4. Join Tables >

5.4.2. Join (High-Speed clip0090 action)

Icon: clip0094

 
Function: Join
 

Property window:

 

clip0095

 

Short description:

Join several tables.

All the joins computed by the Join Action are using the same unique KEY column inside the MASTER table (Use the MultiJoin action if you have many different keys inside the MASTER table).

 

Long Description:

 

ANATEL~2_img8

Pre-requisite

All the input tables must be sorted on the "Key" columns using the SAME sorting algorithm (all "numeric sort" or all "alpha-numeric sort").

 

 

This Action joins several tables on a key. Some definition:
 

The first input pin contains the MASTER table.
 

The other input pins (second, third, etc.) contain the SLAVE tables.

 
During the join, the SLAVE tables are added to (i.e. “joined with”) the MASTER table.

 

 
Please refer to the section “5.1.7. Composite Primary Keys”, to know how to handle composite primary keys.

 

The clip0096 Join Action checks that all the input tables are sorted in the same way (i.e. using the same sorting algorithm). Sorting all the input table is required for the clip0096 Join Action to work (in opposition to the clip0097 MultiJoin Action that does not require any sort at all).

 

 

Because the input tables of the Join Action MUST be sorted for the join to work, this produces an error:
 

ANATEL~3_img216

 
 
..but this will work (provided that the two Sort Actions are properly parameterized):
 

clip0098

 

 
 
ANATEL~2_img8

When you use the "sort action", it modifies the meta-data of the table, so that it now includes the information "this table is now sorted in a specific way". Using this meta-data, the "Join operator" is able to check if the input tables are correctly sorted.

 

 

 
This will work if the two .gel_anatella files are properly sorted:
 

ANATEL~3_img219

 
 
This means that, if you intend to do several join with a specific table, it’s better to SORT and thereafter save the table. In this way, you can easily compute many joins because the table is already sorted in the .gel_anatella file (and, thus, you don’t need to sort it, again and again, anymore).

 

All the input tables used inside a clip0096 Join Action are read simultaneously (in opposition to the clip0097 MultiJoin Action that, first, reads completely all the SLAVE tables and then, once it’s finished, starts reading the MASTER table).

 

The RAM Memory consumption of the clip0096 Join Action is negligible (i.e. very small) (in opposition to the clip0097 MultiJoin Action that, stores completely all the SLAVE tables in RAM).

 

 

ANATEL~2_img8

NOTE:

You can only define one key inside the MASTER TABLE. If you want to define several different keys inside the MASTER TABLE, use the clip0097 MultiJoin Action.

 

 

Let’s give some examples: Here are the input tables:
 

    +------------------+   +-------------------+   +-------------------+

    |  MASTER TABLE(A) |   | SLAVE TABLE 1(B1) |   | SLAVE TABLE 2(B2) |

    +----+------+------+   +-----+------+------+   +-----+------+------+

    |KeyA|Field1|field2|   |KeyB1|Field1|field2|   |KeyB2|Field1|field2|

    +----+------+------+   +-----+------+------+   +-----+------+------+

    |   1|     A|     B|   |    1|     C|     D|   |    1|     E|     F|

    |   2|    AA|    BB|   |    3|    CC|    DD|   |    2|    EE|    FF|

    |   3|   AAA|   BBB|   +-----+------+------+   |    4|   EEE|   FFF|

    |   3|  AAAA|  BBBB|                           +-----+------+------+

    |   5| AAAAA| BBBBB|

    +----+------+------+

 
For the “Left Outer Join” (represented by theANATEL~3_img200  or clip0096 icon), we obtain:
 

    +--------------------------------------------------------------+

    |                          OUTPUT TABLE                        |

    +----+------+------+----------+----------+----------+----------+

    |KeyM|Field1|field2|T01.Field1|T01.field2|T02.Field1|T02.field2|

    +----+------+------+----------+----------+----------+----------+

    |   1|     A|     B|         C|         D|         E|         F|

    |   2|    AA|    BB|          |          |        EE|        FF|

    |   3|   AAA|   BBB|        CC|        DD|          |          |

    |   3|  AAAA|  BBBB|        CC|        DD|          |          |

    |   5| AAAAA| BBBBB|          |          |          |          |

    +----+------+------+----------+----------+----------+----------+

 
 
For the “Full Outer Join” (represented by theclip0099  icon) between Table A and Table B2, we obtain:
 

    +----------------------------------------+

    |             OUTPUT TABLE               |

    +----+------+------+----------+----------+

    |KeyM|Field1|field2|T02.Field1|T02.field2|

    +----+------+------+----------+----------+

    |   1|     A|     B|         E|         F|

    |   2|    AA|    BB|        EE|        FF|

    |   3|   AAA|   BBB|          |          |

    |   3|  AAAA|  BBBB|          |          |

    |   4|      |      |       EEE|       FFF|

    |   5| AAAAA| BBBBB|          |          |

    +----+------+------+----------+----------+

 

 

For the “Inner Join” (represented by theclip0100  icon) between Table A and Table B1, we obtain:
 

    +----------------------------------------+

    |               OUTPUT TABLE             |

    +----+------+------+----------+----------+

    |KeyM|Field1|field2|T01.Field1|T01.field2|

    +----+------+------+----------+----------+

    |   1|     A|     B|         C|         D|

    |   3|   AAA|   BBB|        CC|        DD|

    |   3|  AAAA|  BBBB|        CC|        DD|

    +----+------+------+----------+----------+

 

 

 

For the “A minus B Join” (represented by theclip0101  icon) between Table A and Table B2, we obtain:
 

    +------------------+

    |   OUTPUT TABLE   |

    +----+------+------+

    |KeyM|Field1|field2|

    +----+------+------+

    |   3|   AAA|   BBB|

    |   3|  AAAA|  BBBB|

    |   5| AAAAA| BBBBB|

    +----+------+------+

 

For the “A xor B Join” (represented by theclip0102  icon) between Table A and Table B2, we obtain:
 

    +----------------------------------------+

    |             OUTPUT TABLE               |

    +----+------+------+----------+----------+

    |KeyM|Field1|field2|T02.Field1|T02.field2|

    +----+------+------+----------+----------+

    |   3|   AAA|   BBB|          |          |

    |   3|  AAAA|  BBBB|          |          |

    |   4|      |      |       EEE|       FFF|

    |   5| AAAAA| BBBBB|          |          |

    +----+------+------+----------+----------+

 

 

 

 

ANATEL~2_img8

NOTE:

For the ANATEL~3_img200 clip0099 clip0100 clip0101 clip0102 join, no row of the MASTER TABLE will ever be duplicated.

 

 

ANATEL~2_img8

NOTE:

This Action is actually a good example of meta-data management: the "Join Action" looks at the "meta-data" of the input tables and refuses to join the input tables if the meta-data of the input tables says that "these table are not sorted properly".

 

This operator is an example of clever meta-data management. Most "Anatella-Action" are able to work without using any meta-data at all about the columns of the different tables (i.e. usually the actions don't need to know, for example, if the columns contain numbers or characters). ...but this does NOT mean that Anatella is not able to manipulate and manage meta-data information. On the contrary! For example: when the "Join operator" wants to test the meta-data of the input tables (to see if these input tables are properly sorted), the Anatella framework is providing ALL the required functionalities to do so. Most of the time, with Anatella, you don't need to do any meta-data management but when you really need to do it, it's really easy and powerful.