We’re launching a series of pages and blog posts about our favorite coding language in Marketing Cloud – SQL. We’re starting off with the basics and will look to add some complexity over time. All functions will include real world examples with code to leverage.

What: The SELECT statement starts the Query and is a list of the values you want to display and or write to the data extension.
Why: You need to output at least one value in order for the Query to work effectively.

Example:
Use Case: Cervello needs to feed newly created leads into a Lead Nurture Journey. The Journey sends emails so the entry data extension will need a subscriber key and email address. The emails would also like to be personalized displaying the first name and company of the lead.

(1) A must for any SQL query work in Marketing Cloud is Query Studio. Bonus: it is free: https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FP3yFUAT
(2) The SELECT statement starts off any query

SQL Query: SELECT Tip #1 – Cap all Statements and Functions
When coding in SQL it’s best practice to be consistent. It can also be helpful to separate statements and functions from other code. So whenever possible use all caps.

(3) You only need one variable/field in a query. There’s three different areas to cover with line 2 of this code:
(a) L. designates which data source this variable is coming from as you can see below in the FROM statement we have two databases the Lead synchronized data extension (named AS L) and the Account synchronized data extension (named AS A).
(b) The variable we want to output is a field on the Lead object called ID.
(c) We want to label this variable not as “ID” in our data extension, but as “SubscriberKey” as that’s typically what we use to name the subscriberkey in Marketing Cloud. Using the AS function you can rename any variable.
(4) When adding additional lines always start with a comma.

SQL Query: SELECT Tip #2 – Lines 2+ always start with a comma
Each line in a SELECT statement has to end with a comma. One tip to never forget that comma is to start lines 2+ with a comma so you won’t need to remember to add the comma at the end of the line before.

The Output:

(1) The values from the Lead Synchronized data extension are coming through as SubscriberKey and Email address not ID or Email.
(2) The name of the company or account that is coming from the Account database.
(3) At time of running the CreatedDate field validates that we are only pulling in those records that have been created in the last 24 hours.

Code to copy:

SELECT
L.ID AS SubscriberKey
, L.Email AS EmailAddress
, L.FirstName
, A.Name

FROM Lead_Salesforce AS L
JOIN Account_Salesforce AS A ON L.Company = A.Name

WHERE L.CreatedDate >= DATEADD(DAY, -1, GETDATE())

Links to Functions mentioned in this page:

Function	Link
FROM	https://handsonsfmc.com/how-to-sql-marketing-cloud-from/
WHERE	https://handsonsfmc.com/how-to-sql-marketing-cloud-where/
JOIN	https://handsonsfmc.com/how-to-sql-marketing-cloud-join/

Look out for monthly posts on other key functions in SQL.

Resources:
There’s only one, the best Mateusz Dabrowski…https://mateuszdabrowski.pl/docs/sql/sfmc-sql-basics/