Which Relation/JOIN types are supported in DESelect Segment? – DESelect

In this article, we will explore the different types of MATCHES (this is how SQL JOINs are called in DESelect Segment) and what the results look like when applying a certain type of MATCH(JOIN) between two Data Extensions. Here's a table showing how each of the SQL JOINs is named in DESelect 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.

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

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.

Let's see what the results are when a particular MATCH (JOIN) is applied to these two Data Extensions.

**Customers WITH/WITHOUT MATCHING Orders (LEFT JOIN)**

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	john@microsoft.com	New	1	28734	$1983
2	lisa@microsoft.com	Returning	2	49182	$287
2	lisa@microsoft.com	Returning	2	49180	$80
3	deedee@deselect.com	New	(empty)	(empty)	(empty)

Note: Lisa will be duplicated because her record matches with 2 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

Customers WITH MATCHING Orders (INNER JOIN)

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	john@microsoft.com	New	1	28734	$1983
2	lisa@microsoft.com	Returning	2	49182	$287
2	lisa@microsoft.com	Returning	2	49180	$80

Orders WITH/WITHOUT MATCHING Customer (RIGHT JOIN)

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	john@microsoft.com	New	1	28734	$1983
2	lisa@microsoft.com	Returning	2	49182	$287
2	lisa@microsoft.com	Returning	2	49180	$80
(empty)	(empty)	(empty)	5	1652	$718

Customers WITHOUT MATCHING Orders (LEFT OUTER JOIN)

Expected results: only the Customer records with no matching Order.

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
3	deedee@deselect.com	New	(empty)	(empty)	(empty)

**Customers WITH ALL Orders (FULL OUTER JOIN)**

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	john@microsoft.com	New	1	28734	$1983
2	lisa@microsoft.com	Returning	2	49182	$287
2	lisa@microsoft.com	Returning	2	49180	$80
3	deedee@deselect.com	New	(empty)	(empty)	(empty)
(empty)	(empty)	(empty)	5	1652	$718

**Orders WITHOUT MATCHING Customers (RIGHT OUTER JOIN)**

Expected results: only the Order records with no matching Customer

Customer columns			Order columns
Id	Email	Type	CustomerId	Order Number	Amount Order
(empty)	(empty)	(empty)	5	1652	$718

EACH FROM Customer WITH EACH FROM Orders (CROSS JOIN)

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	john@microsoft.com	New	1	28734	$1983
1	john@microsoft.com	New	2	49182	$287
1	john@microsoft.com	New	2	49180	$80
1	john@microsoft.com	New	5	1652	$718
2	lisa@microsoft.com	Returning	1	28734	$1983
2	lisa@microsoft.com	Returning	2	49182	$287
2	lisa@microsoft.com	Returning	2	49180	$80
2	lisa@microsoft.com	Returning	5	1652	$718
3	deedee@deselect.com	New	1	28734	$1983
3	deedee@deselect.com	New	2	49182	$287
3	deedee@deselect.com	New	2	49180	$80
3	deedee@deselect.com	New	5	1652	$718

Customers WITH/WITHOUT MATCHING Orders (LEFT JOIN)