Join / Manipulation Layer

Keep all left table rows with matching data from right table where available.

Set notation: $$\{(l,r):l\in L,r\in R\cup \{\text{null}\},l.\text{key}=r.\text{key}\}$$

Example:

Left Table:

Right Table:

Result (LEFT JOIN):

Key characteristics:

• Preserves all left table rows
• Includes matching right table data
• Fills non-matches with null
• Maintains left table order

Common applications:

• Enriching base dataset
• Optional relationship handling
• Master data lookup
• Preserving core records

Keep only rows that have matching keys in both tables.

Set notation: $$\{(l,r):l\in L,r\in R,l.\text{key}=r.\text{key}\}$$

Example:

Left Table:

Right Table:

Result (INNER JOIN):

Key characteristics:

• Only includes matching rows
• No null values in join columns
• Maintains left table order
• Potentially fewer rows than either input

Common applications:

• Strict relationship enforcement
• Guaranteed complete records
• Data validation
• Matched pair analysis

Keep all rows from both tables, matching where possible.

Set notation: $$\{(l,r):l\in L\cup \{\text{null}\},r\in R\cup \{\text{null}\},l.\text{key}=r.\text{key}\text{ or }l=\text{null}\text{ or }r=\text{null}\}$$

Example:

Left Table:

Right Table:

Result (FULL OUTER JOIN):

Key characteristics:

• Preserves all rows from both tables
• Matches where possible
• Fills non-matches with null
• Shows complete relationship view

Common applications:

• Data completeness analysis
• Relationship gap detection
• Full data integration
• Migration validation

Cartesian product - combine every row with every row.

Set notation: $$\{(l,r):l\in L,r\in R\}$$

Example:

Left Table:

Right Table:

Result (CROSS JOIN):

Key characteristics:

• No key matching required
• Result size = left rows × right rows
• No null values
• All possible combinations

Common applications:

• Generating all possibilities
• Feature combination creation
• Product variations
• Multi-factor analysis

Keep left table rows that have matches in right table.

Set notation: $$\{l:l\in L,\exists r\in R,l.\text{key}=r.\text{key}\}$$

Example:

Left Table:

Right Table:

Result (SEMI JOIN):

Key characteristics:

• Only keeps matching left rows
• Maintains left table structure
• No duplicates
• No right table columns

Common applications:

• Filtering by existence
• Finding matched records
• Data subset selection
• Relationship verification

Keep left table rows that have no matches in right table.

Set notation: $$\{l:l\in L,\nexists r\in R,l.\text{key}=r.\text{key}\}$$

Example:

Left Table:

Right Table:

Result (ANTI JOIN):

Key characteristics:

• Only keeps non-matching left rows
• Maintains left table structure
• No right table columns
• Inverse of semi join

Common applications:

• Finding missing relationships
• Data gap analysis
• Exception identification
• Quality control checks

No uniqueness requirements:

• Multiple matches allowed
• No validation performed
• Most flexible option

Unique keys in both tables:

• One-to-one relationships
• No duplicates allowed
• Strict 1:1 mapping

Unique keys in right table only:

• Many-to-one relationships
• Right side as lookup table
• Common for reference data

Unique keys in left table only:

• One-to-many relationships
• Left side as master record
• Common for parent-child data