Combining 3 SQL Queries into a Single Query

Question

I'm facing a challenge with my SQL query in Marketing Cloud and would appreciate the community's help in resolving it.

Recently, I created three separate Data Extensions as part of a segmentation. To achieve this, I ran three different SQL queries. The goal is to merge these three queries into a single query to store the resulting data in a single Data Extension.

Here are the three queries I used:

1.  Select FirstName, LastName, city, Subscriberkey, 
    EmailAdress
   
    FROM [Segment1]
   
    WHERE City IS NULL 
   
    OR City NOT LIKE '%Denver%'
   

2.  Select FirstName, LastName, city, Subscriberkey, EmailAdress
    FROM [Segment1]
    WHERE City LIKE '%Denver%'
    and EmailAddress NOT IN 
     (Select EmailAddress from Contacts_segment)
   

3.   Select FirstName, LastName, city, Subscriberkey, EmailAddress
     from [Segment2]
     WHERE 
     EmailAddress NOT IN 
     (Select EmailAddress from [Contacts_segment]
     UNION
     Select EmailAddress from [Contact_data])
   

Here is my attempt to combine these 3 queries into a single query:

    SELECT
    
    FirstName, LastName, city, Subscriberkey, EmailAddress         
    
    'Segment1' AS Segment 
    
    FROM [Segment1]
    
    WHERE City IS NULL 
    
    OR City NOT LIKE '%Denver%'

     UNION

    SELECT

     FirstName, LastName, city, Subscriberkey, EmailAddress
 
    'Segment2' AS Segment 

    FROM [Segment1]

   WHERE City LIKE '%Denver%'

   UNION 

    SELECT 

    FirstName, LastName, city, Subscriberkey, EmailAddress
 
   'Segment3' AS Segment 

    FROM [Segment2]

Note: The fields in my target Data Extension are the same as the ones I'm selecting in my SELECT statement (FirstName, LastName, City, Subscriberkey, EmailAddress), with the only difference being the inclusion of a field (Segment) to identify which segment the contacts are coming from. My only primary key is the Subscriberkey to ensure that they are unique contacts.

I'm facing an issue where the final numbers in the resulting Data Extension do not match the sum of the three original Data Extensions. There seems to be some problem with my final query, but I can't identify it. The situation may be a bit confusing, but I'm confident that someone here can help me resolve this issue. Any suggestions or guidance would be greatly appreciated. Thank you for your patience, I'm new to Marketing Cloud and I'm trying to learn more.

Answer 1

I'd suggest that you build a simple model outside of Marketing Cloud first -- instead of trying match counts. It's easier to look at the data and determine the desired outcomes before writing the query.

Here's a simplified mock-up query using dbfiddle:

1. Prepare the sample tables and add rows to them

CREATE TABLE Segment1 (City varchar(40), Subscriberkey varchar(254), EmailAddress varchar(254));
CREATE TABLE Segment2 (City varchar(40), Subscriberkey varchar(254), EmailAddress varchar(254));
CREATE TABLE Contacts_segment (EmailAddress varchar(254));
CREATE TABLE Contact_Data (EmailAddress varchar(254));
INSERT INTO  Segment1 (City,Subscriberkey,EmailAddress) VALUES 
  (null,'SK1','[email protected]')
, ('Boulder','SK2','[email protected]')
, ('Aurora','SK3','[email protected]')
, ('Lakewood','SK4','[email protected]')
, ('Denver','SK5','[email protected]')
INSERT INTO  Segment2 (City,Subscriberkey,EmailAddress) VALUES 
  ('Durango','SK6','[email protected]')
, ('Denver','SK7','[email protected]')
, ('Aurora','SK8','[email protected]')
, ('Aspen','SK9','[email protected]')
, ('Denver','SK0','[email protected]')
INSERT INTO  Contacts_segment (EmailAddress) VALUES 
 ('[email protected]')
INSERT INTO  Contact_data (EmailAddress) VALUES 
 ('[email protected]')

2. Run your test queries against the sample data:

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment1' AS Segment
FROM Segment1
WHERE
City IS NULL 
OR City NOT LIKE '%Denver%'

UNION

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment2' AS Segment
FROM Segment1
WHERE 
City LIKE '%Denver%'
and EmailAddress NOT IN (
  Select
  EmailAddress
  from Contacts_segment
)

UNION

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment3' AS Segment
FROM Segment2
where
EmailAddress NOT IN (

  Select
  EmailAddress
  from Contacts_segment

  UNION

  Select
  EmailAddress
  from Contact_data

)

3. Verify the results are as you expected:

City	Subscriberkey	EmailAddress	Segment
null	SK1	[email protected]	Segment1
Aspen	SK9	[email protected]	Segment3
Aurora	SK3	[email protected]	Segment1
Boulder	SK2	[email protected]	Segment1
Denver	SK0	[email protected]	Segment3
Denver	SK5	[email protected]	Segment2
Durango	SK6	[email protected]	Segment3
Lakewood	SK4	[email protected]	Segment1

Try it yourself on dbfiddle