Salesforce Learning
1. SELECT Statement
Use Case: Retrieving data from a data extension.
SELECT * FROM [YourDataExtensionName]
Example: To pull all data from a Data Extension called "Subscribers":
SELECT * FROM [Subscribers]
2. WHERE Clause
Use Case: Filtering records based on specific conditions.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] = 'Value'
Example: Get all subscribers from California.
SELECT * FROM [Subscribers] WHERE [State] = 'California'
3. AND / OR Conditions
Use Case: Combining multiple filters to narrow down your results.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName1] = 'Value1' AND [ColumnName2] = 'Value2'
Example: Get all active subscribers who live in California.
SELECT * FROM [Subscribers] WHERE [Status] = 'Active' AND [State] = 'California'
4. DISTINCT Keyword
Use Case: Eliminating duplicates.
SELECT DISTINCT [ColumnName] FROM [YourDataExtensionName]
Example: Find unique email domains in the subscriber list.
SELECT DISTINCT [EmailDomain] FROM [Subscribers]
5. ORDER BY
Use Case: Sorting the result set.
SELECT * FROM [YourDataExtensionName] ORDER BY [ColumnName] ASC
Example: Get all subscribers ordered by their signup date.
SELECT * FROM [Subscribers] ORDER BY [SignupDate] ASC
6. LIMIT Clause
Use Case: Restricting the number of records returned.
SELECT * FROM [YourDataExtensionName] LIMIT 10
Example: Get the first 10 rows from the "Subscribers" data extension.
SELECT * FROM [Subscribers] LIMIT 10
7. JOIN Operation
Use Case: Combining data from multiple data extensions.
SELECT * FROM [DE1] INNER JOIN [DE2] ON [DE1].[ColumnName] = [DE2].[ColumnName]
Example: Combine "Subscribers" and "Orders" data extensions to find order details for each subscriber.
SELECT * FROM [Subscribers] s INNER JOIN [Orders] o ON s.Email = o.Email
8. INNER JOIN vs LEFT JOIN
Use Case: Fetching related records from two or more data extensions.
SELECT * FROM [DE1] INNER JOIN [DE2] ON [DE1].[ColumnName] = [DE2].[ColumnName]
Example: Show subscribers and their associated order data (only those with orders).
SELECT * FROM [Subscribers] s INNER JOIN [Orders] o ON s.Email = o.Email
For unmatched records, use LEFT JOIN:
SELECT * FROM [Subscribers] s LEFT JOIN [Orders] o ON s.Email = o.Email
9. GROUP BY
Use Case: Grouping records to perform aggregate functions.
SELECT [ColumnName], COUNT(*) FROM [YourDataExtensionName] GROUP BY [ColumnName]
Example: Count how many subscribers are from each state.
SELECT [State], COUNT(*) FROM [Subscribers] GROUP BY [State]
10. HAVING Clause
Use Case: Filtering groups after a GROUP BY clause.
SELECT [ColumnName], COUNT(*) FROM [YourDataExtensionName] GROUP BY [ColumnName] HAVING COUNT(*) > 10
Example: Get states where there are more than 10 subscribers.
SELECT [State], COUNT(*) FROM [Subscribers] GROUP BY [State] HAVING COUNT(*) > 10
11. COUNT() Function
Use Case: Counting rows in a result set.
SELECT COUNT(*) FROM [YourDataExtensionName]
Example: Count the number of active subscribers.
SELECT COUNT(*) FROM [Subscribers] WHERE [Status] = 'Active'
12. SUM() Function
Use Case: Summing up a column of numerical data.
SELECT SUM([ColumnName]) FROM [YourDataExtensionName]
Example: Get the total amount spent by all subscribers.
SELECT SUM([AmountSpent]) FROM [Orders]
13. AVG() Function
Use Case: Calculating the average of a numerical column.
SELECT AVG([ColumnName]) FROM [YourDataExtensionName]
Example: Find the average order amount.
SELECT AVG([OrderAmount]) FROM [Orders]
14. MAX() / MIN() Function
Use Case: Getting the maximum or minimum value in a column.
SELECT MAX([ColumnName]) FROM [YourDataExtensionName]
Example: Find the highest order amount.
SELECT MAX([OrderAmount]) FROM [Orders]
15. BETWEEN Operator
Use Case: Filtering results within a specified range.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] BETWEEN 'Value1' AND 'Value2'
Example: Get all orders placed between January 1st and February 1st.
SELECT * FROM [Orders] WHERE [OrderDate] BETWEEN '2025-01-01' AND '2025-02-01'
16. LIKE Operator
Use Case: Pattern matching in text columns.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] LIKE '%Value%'
Example: Find all subscribers whose email contains "gmail".
SELECT * FROM [Subscribers] WHERE [Email] LIKE '%gmail%'
17. IS NULL / IS NOT NULL
Use Case: Checking for null values in a column.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] IS NULL
Example: Get all subscribers with missing email addresses.
SELECT * FROM [Subscribers] WHERE [Email] IS NULL
18. IN Operator
Use Case: Checking if a column’s value matches any value in a list.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] IN ('Value1', 'Value2', 'Value3')
Example: Get all subscribers from specific states.
SELECT * FROM [Subscribers] WHERE [State] IN ('California', 'Texas', 'New York')
19. CONCAT() Function
Use Case: Combining two or more columns into one.
SELECT CONCAT([FirstName], ' ', [LastName]) AS FullName FROM [YourDataExtensionName]
Example: Combine first and last names into one column.
SELECT CONCAT([FirstName], ' ', [LastName]) AS FullName FROM [Subscribers]
20. DATE Functions
Use Case: Working with dates in SQL.
SELECT * FROM [YourDataExtensionName] WHERE YEAR([DateColumn]) = 2025
Example: Get all orders from the year 2025.
SELECT * FROM [Orders] WHERE YEAR([OrderDate]) = 2025
21. CASE WHEN
Use Case: Conditional logic in SQL queries.
SELECT [ColumnName], CASE WHEN [ColumnName] = 'Value1' THEN 'Result1' ELSE 'Result2' END AS NewColumn FROM [YourDataExtensionName]
Example: Create a new column to categorize order amounts.
SELECT [OrderAmount], CASE WHEN [OrderAmount] > 100 THEN 'High' ELSE 'Low' END AS OrderCategory FROM [Orders]
22. Subquery
Use Case: Using a query inside another query.
SELECT * FROM [YourDataExtensionName] WHERE [ColumnName] IN (SELECT [ColumnName] FROM [AnotherDE])Example: Get subscribers who have placed an order.
SELECT * FROM [Subscribers] WHERE [Email] IN (SELECT [Email] FROM [Orders])23. UNION Operator
Use Case: Combining results from two or more queries.
SELECT [Email] FROM [Subscribers_US]
UNION
SELECT [Email] FROM [Subscribers_UK]
✅ Tip: UNION removes duplicates by default. If you want to include duplicates, use UNION ALL.
24. UPDATE Statement
Use Case: Updating existing records in a data extension.
UPDATE [Subscribers]
SET [Status] = 'Active'
WHERE [Email] = '[email protected]'
✅ Tip: Always test your query in a test data extension before updating real data — SFMC SQL doesn’t have an “undo” option.
25. DELETE Statement
Use Case: Removing unwanted records from a data extension.
DELETE FROM [Subscribers]
WHERE [Status] = 'Unsubscribed'
✅ Tip: Be extra careful — this permanently removes data from your DE.
