INNER JOIN in SQL — Complete Real-World Guide for Salesforce Marketing Cloud (SFMC)

What is INNER JOIN?

INNER JOIN is used to combine data from two tables based on a matching column.

It returns:

ONLY matching records from both tables
Excludes unmatched records
Used heavily in SFMC Query Activities
One of the most important SQL concepts for interviews and real-world projects

Basic Syntax of INNER JOIN

SELECT
    table1.column,
    table2.column
FROM Table1 table1
INNER JOIN Table2 table2
ON table1.CommonColumn = table2.CommonColumn

How INNER JOIN Works

Example Visualization

Customers Table             Orders Table

101                         101   ✅ Match
102                         102   ✅ Match
103                         104   ❌ No Match

INNER JOIN Result:
101
102

Only matching records are returned.

USE CASE 1 — Customers Who Made a Purchase

Scenario

A company wants to send emails only to customers who actually placed an order.

Customers Table

CustomerID	Name
101	Alice
102	Bob
103	Charlie

Orders Table

CustomerID	OrderID
101	5001
102	5002

SQL Query

SELECT
    c.CustomerID,
    c.Name,
    o.OrderID
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID

Output

CustomerID	Name	OrderID
101	Alice	5001
102	Bob	5002

Explanation

Alice and Bob exist in both tables
Charlie exists only in Customers table
Charlie is excluded because no order exists
INNER JOIN keeps only matching rows

Real-World SFMC Usage

Used for:

Purchasers segmentation
Order confirmation journeys
Cross-sell campaigns
Post-purchase automation
Customer retention campaigns

USE CASE 2 — Subscribers Who Filled a Form

Scenario

Marketing team wants to target only users who submitted a lead form.

Subscribers Table

SubscriberID	Email
S001	raj@gmail.com
S002	neha@mail.com
S003	amit@yahoo.com

Form Submissions Table

Email	FormID
raj@gmail.com	F1001
amit@yahoo.com	F1002

SQL Query

SELECT
    s.SubscriberID,
    s.Email,
    f.FormID
FROM Subscribers s
INNER JOIN FormSubmissions f
ON s.Email = f.Email

Output

SubscriberID	Email	FormID
S001	raj@gmail.com	F1001
S003	amit@yahoo.com	F1002

Explanation

Raj and Amit submitted forms
Neha never submitted the form
Since there is no matching form record, Neha is excluded

Real-World SFMC Usage

Used for:

Lead generation campaigns
Webinar registrations
Landing page tracking
Newsletter signup analysis
Sales qualification workflows

USE CASE 3 — Active Users Who Logged In

Scenario

Find only active users who logged into the system.

User Master Table

UserID	Name	Status
U1	Raj	Active
U2	Neha	Active
U3	Deepak	Blocked

Login Table

UserID	LoginTime
U1	2024-01-01 09:00 AM
U3	2024-01-01 10:00 AM

SQL Query

SELECT
    u.UserID,
    u.Name,
    l.LoginTime
FROM UserMaster u
INNER JOIN LoginTable l
ON u.UserID = l.UserID
WHERE u.Status = 'Active'

Output

UserID	Name	LoginTime
U1	Raj	2024-01-01 09:00 AM

Explanation

Raj is active and logged in
Deepak logged in but is blocked
Neha is active but never logged in
INNER JOIN removes users without matching login records
WHERE clause filters only active users

Real-World SFMC Usage

Used for:

Active app user reports
Website login tracking
Portal engagement monitoring
User activity analysis
Product usage journeys

USE CASE 4 — Employees Assigned to a Project

Scenario

HR team wants to identify employees currently assigned to projects.

Employee Table

EmpID	Name
E101	Raju
E102	Meera
E103	Suraj

Assigned Projects Table

EmpID	Project
E101	CRM
E103	ERP

SQL Query

SELECT
    e.EmpID,
    e.Name,
    p.Project
FROM Employee e
INNER JOIN AssignedProjects p
ON e.EmpID = p.EmpID

Output

EmpID	Name	Project
E101	Raju	CRM
E103	Suraj	ERP

Explanation

Raju and Suraj are assigned to projects
Meera has no project assignment
INNER JOIN removes unmatched employees

Real-World SFMC Usage

Used for:

Campaign ownership reports
Journey allocation tracking
Team management systems
Resource planning dashboards
Internal workflow systems

USE CASE 5 — Products That Have Reviews

Scenario

E-commerce company wants products that received customer reviews.

Product Table

ProductID	ProductName
P01	Mobile
P02	Laptop
P03	Tablet

Review Table

ProductID	Rating
P01	5
P03	4

SQL Query

SELECT
    p.ProductID,
    p.ProductName,
    r.Rating
FROM Product p
INNER JOIN Review r
ON p.ProductID = r.ProductID

Output

ProductID	ProductName	Rating
P01	Mobile	5
P03	Tablet	4

Explanation

Mobile and Tablet received reviews
Laptop has no review record
INNER JOIN excludes unmatched products

Real-World SFMC Usage

Used for:

Product recommendation campaigns
Feedback collection journeys
Customer satisfaction analysis
Product engagement segmentation
Review-triggered automations

BONUS USE CASE 6 — Subscribers Who Opened an Email

Scenario

Marketing team wants only subscribers who opened an email.

SQL Query Using SFMC Data Views

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    o.EventDate
FROM Subscribers_DE s
INNER JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey

Explanation

_Open data view stores email open activity
Only subscribers who opened email exist in _Open
Non-openers are automatically excluded

Real-World Usage

Used for:

Engaged audience segmentation
Re-engagement campaigns
Open rate analysis
Journey branching logic
Engagement-based filtering

BONUS USE CASE 7 — Subscribers Who Clicked a Link

SQL Query

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    c.URL
FROM Subscribers_DE s
INNER JOIN _Click c
ON s.SubscriberKey = c.SubscriberKey

Real-World Usage

Used for:

Highly engaged users
Click tracking analysis
Interest-based segmentation
CTA performance reporting
Behavioral targeting

INNER JOIN vs LEFT JOIN

INNER JOIN	LEFT JOIN
Returns only matching records	Returns all left table records
Removes unmatched rows	Keeps unmatched rows
Used for confirmed actions	Used for full reporting
Best for engagement filtering	Best for audit/reporting

Important Interview Points

What interviewer expects

You should know:

Why INNER JOIN is used
How matching works
Difference between INNER JOIN and LEFT JOIN
How to join Data Extensions
How SFMC Data Views work with joins
Why unmatched records disappear

Common SFMC INNER JOIN Scenarios

Business Scenario	Tables Joined
Purchasers only	Customers + Orders
Email openers	Subscribers + _Open
Click tracking	Subscribers + _Click
Journey participants	Contacts + Journey DE
Webinar attendees	Registrations + Attendance
Cart abandoners	Customers + Cart
Form submitters	Contacts + Forms
Loyalty members	Contacts + Loyalty Table

Common Mistakes Beginners Make

Mistake 1 — Wrong Join Column

ON CustomerID = Email

Wrong columns cause incorrect results.

Mistake 2 — Missing ON Condition

INNER JOIN Orders

This creates a Cartesian Product.

Mistake 3 — Using Wrong Alias

SELECT CustomerID

Instead use:

SELECT c.CustomerID

Best Practices in SFMC

Always:

Use table aliases
Use clear join conditions
Filter unnecessary data
Use proper primary keys
Test query with small data first
Avoid duplicate joins

Final Summary

INNER JOIN Rule

INNER JOIN returns ONLY matching records from both tables

Quick Recap

Situation	INNER JOIN Result
Record exists in both tables	Included
Record missing in one table	Excluded
Perfect for engagement filtering	Yes
Common in SFMC	Very Common

Most Common SFMC INNER JOIN Query

SELECT
    s.SubscriberKey,
    s.EmailAddress,
    o.EventDate
FROM Subscribers_DE s
INNER JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
WHERE o.EventDate >= DATEADD(day,-30,GETDATE())

Purpose

Find subscribers who opened an email in the last 30 days.

This is one of the most common real-world SFMC SQL interview questions.

What is LEFT JOIN?

A LEFT JOIN in SQL retrieves:

✅ All records from the LEFT table
✅ Only matching records from the RIGHT table

If there is no matching data in the right table, SQL still keeps the left table record and shows NULL for the right table columns.

🛠️ Why Use LEFT JOIN?

LEFT JOIN is useful because:

✅ Retrieves all data from the left table
✅ Prevents losing important records
✅ Helps identify missing activity/data
✅ Shows both matched and unmatched records
✅ Useful for reporting and engagement analysis

⚙️ How LEFT JOIN Works

When performing a LEFT JOIN between:


Customers_DE → LEFT TABLE
Orders_DE    → RIGHT TABLE

SQL:

Keeps ALL customers
Adds matching order data
Shows NULL if no order exists

📂 Data Extensions

Customers_DE (Customer Details)

CustomerID	Name
101	Alice
102	Bob
103	Charlie
104	Dave

Orders_DE (Order Details)

CustomerID	OrderID
101	5001
102	5002
104	5003

📝 SQL Query


SELECT 
    a.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
LEFT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID

📊 Query Studio Output

CustomerID	Name	OrderID
101	Alice	5001
102	Bob	5002
103	Charlie	NULL
104	Dave	5003

💡 Important Understanding

Why Does Charlie Appear?

Charlie exists in:


Customers_DE

But Charlie does NOT exist in:


Orders_DE

Since LEFT JOIN keeps all records from the LEFT table:

✅ Charlie is still included
❌ No matching order exists
➡️ OrderID becomes NULL

🔄 Visual Understanding


LEFT TABLE               RIGHT TABLE

101                      101   ✅ Match
102                      102   ✅ Match
103                      ❌ No Match
104                      104   ✅ Match

Final LEFT JOIN Result


101
102
103   ← Still Included
104

📌 Important Rule


LEFT JOIN keeps ALL records from the LEFT table

Even if no matching record exists in the right table.

🚀 Real-World SFMC Usage

LEFT JOIN is commonly used in Salesforce Marketing Cloud for:

Customers without purchases
Subscribers who never opened emails
Subscribers who never clicked links
Inactive user tracking
Missing engagement analysis
Journey auditing
Customer retention campaigns

🧪 Practice Task — LEFT JOIN in Query Studio

🎯 Task Objective

Find all customers and check whether they placed an order or not.

📂 Source Data Extensions

Customers_DE

CustomerID	Name
101	Alice
102	Bob
103	Charlie
104	Dave

Orders_DE

CustomerID	OrderID
101	5001
102	5002
104	5003

📝 Run This Query in Query Studio


SELECT 
    a.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
LEFT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID

📊 Final Query Studio Result

CustomerID	Name	OrderID
101	Alice	5001
102	Bob	5002
103	Charlie	NULL
104	Dave	5003

📌 What This Result Means

Customer	Explanation
Alice	Customer placed order
Bob	Customer placed order
Charlie	No order exists → NULL
Dave	Customer placed order

⚠️ Common Beginner Mistake

Wrong ❌


WHERE OrderID = NULL

Correct ✅


WHERE OrderID IS NULL

📚 INNER JOIN vs LEFT JOIN

INNER JOIN	LEFT JOIN
Only matching rows	All left table rows
Removes unmatched records	Keeps unmatched left records
Missing data excluded	Missing data shown as NULL
Used for engagement filtering	Used for complete reporting

🎯 Final Summary

✅ LEFT JOIN preserves all records from the left table
✅ Missing matches appear as NULL
✅ Extremely important in SFMC reporting and segmentation
✅ Commonly used for identifying non-engaged users

💡 Key Takeaway


LEFT JOIN = Keep everything from LEFT table



 
 What is RIGHT JOIN in SFMC?
A RIGHT JOIN in SQL retrieves:
✅ All records from the RIGHT table

✅ Only matching records from the LEFT table
If there is no match in the left table, SQL still keeps the right table record and shows NULL for the left table columns.

🛠️ Why Use RIGHT JOIN in SFMC?
RIGHT JOIN is useful because:
✅ Ensures all right-table records are preserved

✅ Helps identify unmatched left-table data

✅ Useful for order tracking and engagement analysis

✅ Helps audit missing customer information

✅ Shows both matched and unmatched records

⚙️ How RIGHT JOIN Works
When performing a RIGHT JOIN between:
Customers_DE → LEFT TABLE
Orders_DE    → RIGHT TABLE

SQL:


Keeps ALL orders


Adds matching customer data


Shows NULL if customer does not exist



📂 Data Extensions in SFMC
Customers_DE (Customer Details)
CustomerID Name
101 Alice
102 Bob
103 Charlie
104 Dave

Orders_DE (Order Details)
CustomerID OrderID
101 5001
102 5002
104 5003
105 5004

📝 Query in SFMC
SELECT
    b.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
RIGHT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID


📊 Query Studio Output
CustomerID Name OrderID
101 Alice 5001
102 Bob 5002
104 Dave 5003
105 NULL 5004

💡 Important Understanding
Why Does CustomerID 105 Appear?
CustomerID 105 exists in:
Orders_DE

But CustomerID 105 does NOT exist in:
Customers_DE

Since RIGHT JOIN keeps all records from the RIGHT table:
✅ Order 5004 is still included

❌ No matching customer exists

➡️ Name becomes NULL

🔄 Visual Understanding
LEFT TABLE                RIGHT TABLE

101                       101   ✅ Match
102                       102   ✅ Match
103                       ❌ No Match
104                       104   ✅ Match
                          105   ✅ Still Included


📌 Important Rule
RIGHT JOIN keeps ALL records from the RIGHT table

Even if no matching record exists in the left table.

🧪 Practice Task — RIGHT JOIN in Query Studio
🎯 Task Objective
Find all orders and check whether matching customer information exists.

📂 Source Data Extensions
Customers_DE
CustomerID Name
101 Alice
102 Bob
103 Charlie
104 Dave

Orders_DE
CustomerID OrderID
101 5001
102 5002
104 5003
105 5004

📝 Run This Query in Query Studio
SELECT
    b.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
RIGHT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID




📊 Final Query Studio Result



CustomerID Name OrderID
101 Alice 5001
102 Bob 5002
104 Dave 5003
105 NULL 5004

📌 What This Result Means
CustomerID Explanation
101 Matching customer found
102 Matching customer found
104 Matching customer found
105 Order exists but customer missing

🚀 Real-World SFMC Usage
RIGHT JOIN is commonly used for:


Order auditing


Missing customer analysis


Engagement tracking


Data validation


Purchase reconciliation


CRM data integrity checks



⚠️ Common Beginner Mistake
Wrong ❌
WHERE Name = NULL

Correct ✅
WHERE Name IS NULL


📚 LEFT JOIN vs RIGHT JOIN
LEFT JOIN RIGHT JOIN
Keeps all left-table records Keeps all right-table records
Missing right data becomes NULL Missing left data becomes NULL
Used for customer analysis Used for order/activity analysis
Most commonly used Less commonly used

🎯 Final Summary
✅ RIGHT JOIN preserves all records from the right table

✅ Missing left-table matches appear as NULL

✅ Useful for identifying missing customer information

✅ Helps validate order and engagement data

💡 Key Takeaway
RIGHT JOIN = Keep everything from RIGHT table






FULL OUTER JOIN in SFMC (Workaround)
Salesforce Marketing Cloud (SFMC) does NOT support FULL OUTER JOIN directly.
However, we can achieve the same result using:
LEFT JOIN + RIGHT JOIN + UNION


📌 What FULL OUTER JOIN Returns
A FULL OUTER JOIN returns:
✅ All records from the LEFT table

✅ All records from the RIGHT table

✅ Matching records from both tables

✅ NULL values where matches do not exist

🛠️ Why Use FULL OUTER JOIN in SFMC?
FULL OUTER JOIN is useful because:
✅ Ensures no data is lost from either table

✅ Helps analyze all customers and all orders

✅ Useful for reconciliation and audits

✅ Helps identify missing relationships

✅ Combines matched + unmatched records

⚙️ Why SFMC Needs a Workaround
SFMC SQL does not support:
FULL OUTER JOIN

So we simulate it using:
LEFT JOIN
UNION
RIGHT JOIN


📂 Data Extensions in SFMC
Customers_DE (Customer Details)
CustomerID Name
101 Alice
102 Bob
103 Charlie
104 Dave

Orders_DE (Order Details)
CustomerID OrderID
101 5001
102 5002
104 5003
105 5004

📝 Workaround Query in SFMC
SELECT
    a.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
LEFT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID

UNION

SELECT
    b.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
RIGHT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID


📊 Query Studio Output
CustomerID Name OrderID
101 Alice 5001
102 Bob 5002
103 Charlie NULL
104 Dave 5003
105 NULL 5004

💡 Important Understanding
Why Does Charlie Appear?
Charlie exists in:
Customers_DE

But has no order.
So:
✅ Customer data appears

❌ Order data missing

➡️ OrderID becomes NULL

Why Does CustomerID 105 Appear?
CustomerID 105 exists in:
Orders_DE

But does not exist in Customers_DE.
So:
✅ Order data appears

❌ Customer data missing

➡️ Name becomes NULL

🔄 Visual Understanding
Customers_DE               Orders_DE

101                        101   ✅ Match
102                        102   ✅ Match
103                        ❌ No Order
104                        104   ✅ Match
                           105   ✅ No Customer


📌 Important Rule
FULL OUTER JOIN keeps ALL records from BOTH tables


🧪 Practice Task — FULL OUTER JOIN in Query Studio
🎯 Task Objective
Find all customers and all orders, even if matching data does not exist.

📂 Source Data Extensions
Customers_DE
CustomerID Name
101 Alice
102 Bob
103 Charlie
104 Dave

Orders_DE
CustomerID OrderID
101 5001
102 5002
104 5003
105 5004

📝 Run This Query in Query Studio
SELECT
    a.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
LEFT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID

UNION

SELECT
    b.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
RIGHT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID


SELECT
    a.CustomerID,
    a.Name,
    b.OrderID
FROM Customers_DE a
LEFT JOIN Orders_DE b
ON a.CustomerID = b.CustomerID

UNION

SELECT
    b.CustomerID,
    NULL AS Name,
    b.OrderID
FROM Orders_DE b
LEFT JOIN Customers_DE a
ON b.CustomerID = a.CustomerID
WHERE a.CustomerID IS NULL


📊 Final Query Studio Result



CustomerID Name OrderID
101 Alice 5001
102 Bob 5002
103 Charlie NULL
104 Dave 5003
105 NULL 5004

📌 How This Works
Join Type Purpose
LEFT JOIN Gets all customers
RIGHT JOIN Gets all orders
UNION Combines both results

🚀 Real-World SFMC Usage
FULL OUTER JOIN workaround is commonly used for:


Customer-order reconciliation


CRM data validation


Missing relationship analysis


Order auditing


Subscriber engagement comparison


Data quality checks



⚠️ Common Beginner Mistake
Using only:
LEFT JOIN

or
RIGHT JOIN

will NOT return complete data from both tables.

📚 LEFT JOIN vs RIGHT JOIN vs FULL OUTER JOIN
LEFT JOIN RIGHT JOIN FULL OUTER JOIN
Keeps left table Keeps right table Keeps both tables
Missing right data → NULL Missing left data → NULL Missing values on both sides
Most commonly used Less common Workaround needed in SFMC

🎯 Final Summary
✅ SFMC does not support FULL OUTER JOIN directly

✅ Use LEFT JOIN + RIGHT JOIN + UNION

✅ Preserves all records from both tables

✅ Helps identify missing relationships

✅ Extremely useful for reconciliation and auditing

💡 Key Takeaway
FULL OUTER JOIN in SFMC =
LEFT JOIN + RIGHT JOIN + UNION


SELF JOIN in SFMC – Handling Hierarchical Data
A SELF JOIN is when a table joins to itself using aliases.
This is useful for hierarchical relationships such as:
✅ Manager → Employee structures

✅ Parent → Child categories

✅ Referral systems

✅ Organizational hierarchy

✅ Multi-level customer relationships

📌 What Makes SELF JOIN Different?
Normally:
Table A joins Table B

But in SELF JOIN:
Table joins itself

using aliases.

Why Use SELF JOIN in SFMC?
SELF JOIN is useful because:
✅ Helps analyze hierarchical relationships

✅ Useful for referral programs

✅ Helps map reporting structures

✅ Connects related records within same DE

✅ Efficiently analyzes parent-child data

⚙️ Example — Employee Hierarchy
Suppose we have:
Employees_DE

where:
ManagerID

references another employee inside the SAME table.

📂 Employees_DE (Employee Details)
EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Charlie 1
4 Dave 2
5 Eve 2

📌 Understanding the Data
Employee Reports To
Alice No Manager
Bob Alice
Charlie Alice
Dave Bob
Eve Bob

🔄 Visual Hierarchy
Alice
 ├── Bob
 │    ├── Dave
 │    └── Eve
 └── Charlie


📝 SELF JOIN Query in SFMC
SELECT
    e1.EmployeeID,
    e1.Name AS EmployeeName,
    e2.Name AS ManagerName
FROM Employees_DE e1
LEFT JOIN Employees_DE e2
ON e1.ManagerID = e2.EmployeeID


📊 Query Studio Output
EmployeeID EmployeeName ManagerName
1 Alice NULL
2 Bob Alice
3 Charlie Alice
4 Dave Bob
5 Eve Bob

💡 How This Query Works
e1 Alias
Represents:
Employee records


e2 Alias
Represents:
Manager records


Join Condition
e1.ManagerID = e2.EmployeeID

Meaning:
Employee's ManagerID
matches
Manager's EmployeeID


📌 Why LEFT JOIN is Used
Using:
LEFT JOIN

ensures employees without managers still appear.
Example:
Employee Manager
Alice NULL

🧪 Practice Task — SELF JOIN in Query Studio
🎯 Task Objective
Find all employees and display their managers.

📂 Source Data Extension
Employees_DE
EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Charlie 1
4 Dave 2
5 Eve 2

📝 Run This Query in Query Studio
SELECT
    e1.EmployeeID,
    e1.Name AS EmployeeName,
    e2.Name AS ManagerName
FROM Employees_DE e1
LEFT JOIN Employees_DE e2
ON e1.ManagerID = e2.EmployeeID


📊 Final Query Studio Result



EmployeeID EmployeeName ManagerName
1 Alice NULL
2 Bob Alice
3 Charlie Alice
4 Dave Bob
5 Eve Bob

🚀 Real-World SFMC Usage
SELF JOIN is commonly used for:


Referral programs


MLM/customer hierarchies


Employee reporting structures


Parent-child category mapping


Account relationships


Organization structures



📌 Example — Referral Program
Customer Referred By
John Mike
Sarah John
A SELF JOIN can identify:
✅ Who referred whom

✅ Referral chains

✅ Referral rewards eligibility

⚠️ Common Beginner Mistake
Wrong ❌
FROM Employees_DE
JOIN Employees_DE

Without aliases, SQL gets confused.

✅ Correct Way
FROM Employees_DE e1
LEFT JOIN Employees_DE e2

Always use aliases in SELF JOIN.

📚 INNER JOIN vs SELF JOIN
INNER JOIN SELF JOIN
Joins different tables Joins same table
Used for relationships between tables Used for hierarchical relationships
Common in transactional data Common in organizational data

🎯 Final Summary
✅ SELF JOIN joins a table to itself

✅ Uses aliases to differentiate roles

✅ Ideal for hierarchical relationships

✅ Common in employee and referral systems

✅ LEFT JOIN helps preserve top-level records

💡 Key Takeaway
SELF JOIN = A table joining itself using aliases

CustomerID	Explanation
101	Matching customer found
102	Matching customer found
104	Matching customer found
105	Order exists but customer missing

LEFT JOIN	RIGHT JOIN
Keeps all left-table records	Keeps all right-table records
Missing right data becomes NULL	Missing left data becomes NULL
Used for customer analysis	Used for order/activity analysis
Most commonly used	Less commonly used

Join Type	Purpose
LEFT JOIN	Gets all customers
RIGHT JOIN	Gets all orders
UNION	Combines both results

LEFT JOIN	RIGHT JOIN	FULL OUTER JOIN
Keeps left table	Keeps right table	Keeps both tables
Missing right data → NULL	Missing left data → NULL	Missing values on both sides
Most commonly used	Less common	Workaround needed in SFMC

INNER JOIN	SELF JOIN
Joins different tables	Joins same table
Used for relationships between tables	Used for hierarchical relationships
Common in transactional data	Common in organizational data

Complete SFMC SQL Joins Guide with Practice Tasks

INNER JOIN in SQL — Complete Real-World Guide for Salesforce Marketing Cloud (SFMC)

What is INNER JOIN?

Basic Syntax of INNER JOIN

How INNER JOIN Works

Example Visualization

USE CASE 1 — Customers Who Made a Purchase

Scenario

Customers Table

Orders Table

SQL Query

Output

Explanation

Real-World SFMC Usage

USE CASE 2 — Subscribers Who Filled a Form

Scenario

Subscribers Table

Form Submissions Table

SQL Query

Output

Explanation

Real-World SFMC Usage

USE CASE 3 — Active Users Who Logged In

Scenario

User Master Table

Login Table

SQL Query

Output

Explanation

Real-World SFMC Usage

USE CASE 4 — Employees Assigned to a Project

Scenario

Employee Table

Assigned Projects Table

SQL Query

Output

Explanation

Real-World SFMC Usage

USE CASE 5 — Products That Have Reviews

Scenario

Product Table

Review Table

SQL Query

Output

Explanation

Real-World SFMC Usage

BONUS USE CASE 6 — Subscribers Who Opened an Email

Scenario

SQL Query Using SFMC Data Views

Explanation

Real-World Usage

BONUS USE CASE 7 — Subscribers Who Clicked a Link

SQL Query

Real-World Usage

INNER JOIN vs LEFT JOIN

Important Interview Points

What interviewer expects

Common SFMC INNER JOIN Scenarios

Common Mistakes Beginners Make

Mistake 1 — Wrong Join Column

Mistake 2 — Missing ON Condition

Mistake 3 — Using Wrong Alias

Best Practices in SFMC

Always:

Final Summary

INNER JOIN Rule

Quick Recap

Most Common SFMC INNER JOIN Query

Purpose

What is LEFT JOIN?

🛠️ Why Use LEFT JOIN?

LEFT JOIN is useful because:

⚙️ How LEFT JOIN Works

📂 Data Extensions

Customers_DE (Customer Details)

Orders_DE (Order Details)

📝 SQL Query

📊 Query Studio Output

💡 Important Understanding

Why Does Charlie Appear?