Using SQL in SFMC (With Examples)

SQL is one of the most valuable skills you can develop for Salesforce Marketing Cloud. It is how you segment with precision, transform raw data into sendable audiences, query tracking data from SFMC’s data views, and automate data pipelines that would otherwise require manual exports and imports.

This guide covers how SQL works in SFMC, what makes it different from standard SQL, and the real query patterns you will use most often — with examples you can adapt and use directly.

How SQL Works in SFMC

SQL in SFMC runs inside Query Activity in Automation Studio. A Query Activity takes a SQL SELECT statement, runs it against your data extensions and SFMC system data views, and writes the results into a target data extension.

To access it: Automation Studio → Activities → SQL Query → Create Activity

The output of every query is a data extension — which you can then use for sending, as a journey entry source, or as an input to the next activity in an automation chain.

How SFMC SQL Differs from Standard SQL

SFMC uses a subset of T-SQL (Transact-SQL) with important limitations:

Every SFMC SQL query must write its results to a target data extension. You cannot query without specifying where the output goes.

Accessing and Creating a SQL Query Activity

1. Go to Automation Studio → Activities
2. Click SQL Query → Create Activity
3. Name your activity using your account naming convention — for example SEG_ReEngagement_90Days
4. Write your SQL in the query editor
5. Select your target data extension where results will be written
6. Choose Overwrite (replace all records) or Update (add/update records) as your write mode
7. Click Validate to check for syntax errors before saving

Validated queries can be run manually from the activity page or scheduled inside an Automation Studio automation.

Example 1: Basic SELECT from a Data Extension

The simplest query — pull all active subscribers from a data extension:

SELECT
  ContactKey,
  EmailAddress,
  FirstName,
  LastName
FROM Contact_Data
WHERE Status = 'Active'

Data extension names in SFMC go in the FROM clause without brackets for most queries. If your data extension name contains spaces, wrap it in square brackets: FROM [Contact Data].

Example 2: JOIN Two Data Extensions

Pull subscriber details alongside their loyalty tier from a separate data extension:

SELECT
  c.ContactKey,
  c.EmailAddress,
  c.FirstName,
  l.LoyaltyTier,
  l.PointsBalance
FROM Contact_Data c
INNER JOIN Loyalty_Data l
  ON c.ContactKey = l.ContactKey
WHERE l.LoyaltyTier IN ('Gold', 'Silver')

Use aliases (c, l) to keep queries readable when joining multiple tables. INNER JOIN returns only contacts that exist in both data extensions. Use LEFT JOIN if you want all contacts from the left table regardless of whether they have a matching record in the right table.

Example 3: Segment by Engagement — Recent Openers

Pull contacts who opened an email in the last 30 days, using SFMC’s _Open data view:

SELECT DISTINCT
  s.SubscriberKey,
  s.EmailAddress
FROM _Open o
INNER JOIN Contact_Data s
  ON o.SubscriberKey = s.ContactKey
WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE())
  AND o.IsUnique = 1

_Open is one of SFMC’s built-in data views — system tables that store tracking events. IsUnique = 1 filters to unique opens only, excluding repeat opens of the same send by the same subscriber.

Example 4: Identify Unengaged Subscribers

Pull contacts who have not opened any email in the last 90 days — for a re-engagement journey or suppression list:

SELECT
  c.ContactKey,
  c.EmailAddress,
  c.FirstName
FROM Contact_Data c
WHERE c.ContactKey NOT IN (
  SELECT DISTINCT SubscriberKey
  FROM _Open
  WHERE EventDate >= DATEADD(DAY, -90, GETDATE())
)

This subquery pattern is common in SFMC segmentation — identify who is NOT in a behaviour set. Use it for suppression lists, re-engagement audiences, and sunset segments.

Example 5: Query Send and Click Data Together

Pull a combined engagement summary — sends, opens, and clicks per subscriber — from SFMC data views:

SELECT
  s.SubscriberKey,
  s.EmailAddress,
  COUNT(DISTINCT se.JobID) AS TotalSends,
  COUNT(DISTINCT o.JobID) AS TotalOpens,
  COUNT(DISTINCT cl.JobID) AS TotalClicks
FROM Contact_Data s
LEFT JOIN _Sent se ON s.ContactKey = se.SubscriberKey
LEFT JOIN _Open o ON s.ContactKey = o.SubscriberKey AND o.IsUnique = 1
LEFT JOIN _Click cl ON s.ContactKey = cl.SubscriberKey AND cl.IsUnique = 1
WHERE se.EventDate >= DATEADD(DAY, -90, GETDATE())
GROUP BY s.SubscriberKey, s.EmailAddress

This query gives you a subscriber-level engagement score you can write to a data extension and use for dynamic segmentation or personalisation.

Example 6: CASE WHEN for Conditional Segmentation

Assign a segment label based on engagement level using CASE WHEN:

SELECT
  c.ContactKey,
  c.EmailAddress,
  CASE
    WHEN o.OpenCount >= 5 THEN 'Highly Engaged'
    WHEN o.OpenCount BETWEEN 1 AND 4 THEN 'Moderately Engaged'
    ELSE 'Unengaged'
  END AS EngagementSegment
FROM Contact_Data c
LEFT JOIN (
  SELECT SubscriberKey, COUNT(*) AS OpenCount
  FROM _Open
  WHERE EventDate >= DATEADD(DAY, -90, GETDATE())
    AND IsUnique = 1
  GROUP BY SubscriberKey
) o ON c.ContactKey = o.SubscriberKey

The result writes three segment labels into your target data extension — which you can then use as a Journey Builder entry condition or AMPscript variable for personalised content.

Example 7: Filter by Date Range

Pull contacts who made a purchase in the last 7 days from a transactions data extension:

SELECT
  t.ContactKey,
  t.EmailAddress,
  t.PurchaseDate,
  t.OrderValue,
  t.ProductCategory
FROM Transactions_Data t
WHERE t.PurchaseDate >= DATEADD(DAY, -7, GETDATE())
  AND t.OrderValue > 0
ORDER BY t.PurchaseDate DESC

DATEADD(DAY, -7, GETDATE()) is the standard SFMC pattern for relative date filtering. Replace -7 with any number of days, or use MONTH or YEAR as the date part.

SFMC Data Views: The System Tables You Need to Know

Data views are SFMC’s built-in system tables for tracking data. They are available in SQL queries but are not visible in the Data Extensions UI.

Data views only retain data for 6 months by default. For longer-term reporting, write data view data to a permanent data extension regularly via a scheduled Query Activity.

Running and Scheduling SQL Queries

Manual run: From the Query Activity page, click Run to execute immediately. Use this for ad-hoc segmentation or testing.

Scheduled via Automation: Add your Query Activity to an Automation Studio automation and set a schedule — hourly, daily, weekly, or on file drop. This is the recommended approach for any query that feeds a recurring send or journey entry source.

Chaining activities: In Automation Studio, chain a Query Activity directly into an Email Send Activity — query runs first, results write to the target data extension, then the send fires to that audience. All in one automated workflow.

For a broader look at how Automation Studio and Journey Builder work together with SQL-driven segmentation, the Automation Studio vs Journey Builder guide covers the full picture.

Common SQL Errors in SFMC and How to Fix Them

“Invalid object name” — the data extension name in your FROM clause does not match exactly. Check spelling, capitalisation, and whether the data extension is in a shared folder or business unit your query can access.

“Column does not exist” — the field name in your SELECT or WHERE clause does not match the data extension field name exactly. SFMC field names are case-insensitive but must match the exact spelling.

Query times out — your query is scanning too much data. Add a WHERE clause to limit the date range, or use TOP N to limit rows during testing: SELECT TOP 100 * FROM Contact_Data.

Target data extension field mismatch — your SELECT returns fields that do not exist in the target data extension, or the data types do not match. The target data extension must have a matching field for every column your query returns.

Conclusion

SQL is the layer between raw data in SFMC and the precise, behaviour-driven audiences that drive real campaign performance. The examples in this guide cover the patterns you will use in almost every SFMC SQL workflow — basic selects, joins, engagement filtering, data view queries, and conditional segmentation.

Start with Example 1 and Example 3. Build from there. The more comfortable you become with data views and joins, the more of SFMC’s segmentation capability you can actually use.

For help building SQL queries for your specific SFMC data model or setting up automated data pipelines, get in touch →

References: SQL Query Activity — Salesforce Help