Matching Types

This table shows how each of the SQL JOINs is named in Unaric Segment and how the results are represented as the green shaded area in a Venn diagram. Below the table, you will see what the results are when each of these MATCHes is applied to two sample Data Extensions.

Name	SQL Query	Description
A WITH/WITHOUT MATCHING B	LEFT JOIN	Return all the records from (A) with or without matching data from records in (B)
A WITH MATCHING B	INNER JOIN	Return only the records from (A) and (B) with a matching value in the matching field
B WITH/WITHOUT MATCHING A	RIGHT JOIN	Return all the records from (B) with or without matching data from records in (A)
A WITH ALL B	FULL OUTER JOIN	Return all records from (A) and (B), with or without matching data from the opposite Data Extension
A WITHOUT MATCHING B	LEFT OUTER JOIN	Return the records in (A) that don’t have a matching record in (B)
B WITHOUT MATCHING A	RIGHT OUTER JOIN	Return the records in (B) that don’t have a matching record in (A)
EACH FROM A WITH EACH FROM B	CROSS JOIN	Returns for each and every record in (A) a paired combination with each and every record in (B)

Examples

For explanation purposes, the following two Data Extensions will be used: Customer and Orders. They contain customer and order information, respectively, and these two Data Extensions will be MATCHed (JOINed) on the fields Customer.Id = Orders.Customer Id.

The examples show the results when a particular MATCH (JOIN) is applied to these two Data Extensions.

The customer Lisa will be duplicated because her record matches with two orders, so she will appear twice, once with the information of each order she did. This will happen in every matching relationship between Customers and Orders.

Expected results: all the Customer records with or without a matching Order.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
1	[email protected]	New	1	28734	$1983
2	[email protected]	Returning	2	49182	$287
2	[email protected]	Returning	2	49180	$80
3	[email protected]	New	(empty)	(empty)	(empty)

Expected results: only the Customer and Order records with matching values in the Id/Customer Id columns.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
1	[email protected]	New	1	28734	$1983
2	[email protected]	Returning	2	49182	$287
2	[email protected]	Returning	2	49180	$80

Expected results: all the Order records with or without a matching Customer.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
1	[email protected]	New	1	28734	$1983
2	[email protected]	Returning	2	49182	$287
2	[email protected]	Returning	2	49180	$80
(empty)	(empty)	(empty)	5	1652	$718

Expected results: all the Customer and Order records with or without matching data from the other Data Extension.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
1	[email protected]	New	1	28734	$1983
2	[email protected]	Returning	2	49182	$287
2	[email protected]	Returning	2	49180	$80
3	[email protected]	New	(empty)	(empty)	(empty)
(empty)	(empty)	(empty)	5	1652	$718

Expected results: all Customer records paired with each and every Orders record.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
1	[email protected]	New	1	28734	$1983
1	[email protected]	New	2	49182	$287
1	[email protected]	New	2	49180	$80
1	[email protected]	New	5	1652	$718
2	[email protected]	Returning	1	28734	$1983
2	[email protected]	Returning	2	49182	$287
2	[email protected]	Returning	2	49180	$80
2	[email protected]	Returning	5	1652	$718
3	[email protected]	New	1	28734	$1983
3	[email protected]	New	2	49182	$287
3	[email protected]	New	2	49180	$80
3	[email protected]	New	5	1652	$718

Matching Types

Examples

Customers WITH/WITHOUT MATCHING Orders (LEFT JOIN)

Customers WITH MATCHING Orders (INNER JOIN)

Orders WITH/WITHOUT MATCHING Customer (RIGHT JOIN)

Customers WITHOUT MATCHING Orders (LEFT OUTER JOIN)

Customers WITH ALL Orders (FULL OUTER JOIN)

Orders WITHOUT MATCHING Customers (RIGHT OUTER JOIN)

EACH FROM Customer WITH EACH FROM Orders (CROSS JOIN)