The Join Function is used to combine tables based on common fields, also known as join clauses or predicates. It allows you to create a unified dataset from multiple tables, facilitating enhanced data visibility, and complex queries.
Key Aspects of Join Function:
- Combining Tables: Integrate data from two or more tables.
- Join Clauses/Predicates: Define the criteria for how tables are related and combined.
- Comprehensive Data Analysis: Gain insights that require data from different sources.
Overview of Join Types
The Universal Model supports several types of joins, each with distinct functionalities:
- Cross Join
- Full Join
- Inner Join
- Left Join
- Look Up
- Union All
Cross Join
A Cross Join produces a Cartesian product of the two tables, meaning all possible combinations of rows from both tables are returned. This type of join does not use any join predicates.
Characteristics
- Combines All Rows: Each row from the first table is combined with every row from the second table.
- No Join Condition: Results in a large dataset as it includes all possible combinations.
Usage: Generally used for specific scenarios requiring all combinations of data, but be cautious of the potential for large outputs.
Full Join (Full Outer Join)
A Full Join returns all records from both tables, including matched and unmatched rows. Unmatched rows have NULL values for columns from the other table.
Characteristics
- Includes All Records: Returns both matched and unmatched rows from both tables.
- NULL Values: Missing matches in each table are filled with NULL values in the result set.
Usage: Useful for comprehensive data integration when you must retain all records, regardless of matches.
Inner Join
An Inner Join returns only the rows where there is a match in both tables based on the join predicate.
Characteristics
- Matches Only: Includes only rows with matching values in both tables.
- Excludes Unmatched: Rows without a match are excluded from the result set.
Usage: Commonly used to retrieve related data where matches are required for both tables.
Left Join (Left Outer Join)
A Left Join returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL from the right table.
Characteristics
- All Left Records: Includes all rows from the left table.
- Matched Right Rows: Includes matched rows from the right table.
- NULLs for Unmatched: Unmatched rows from the right table are represented with NULLs.
Usage: Useful when you must include all records from the left table, with or without matches in the right table.
Look Up
A Look Up join is used to find and bring in more data columns from another table based on a matching key.
Characteristics
- Key-Based: Combines data based on a specific key.
- Additional Data: Retrieves extra data columns to supplement the primary data.
Union All
A Union All combines the result sets of two or more tables, including all duplicates.
Characteristics
- Combines Rows: Includes all rows from all tables in the result set.
- Duplicates Included: Does not remove duplicate rows.
Usage: Useful when you must consolidate data from multiple tables without removing duplicates.
This is the example of the left outer join. The same example that we are using in our upcoming demo video. Input 1 data is used as a leading (right) table and Input 2 data as a left table in the left outer join approach.
These two tables were joined based on the Company column as a join predicate column. Based on common values in this column, all extra columns from the right table were added on top of the left one, which can be seen in the final result table at the bottom of the screen.