Overcoming the limits of SOQL

If you've spent significant time in Salesforce, you've likely used Salesforce Object Query Language - or "SOQL", Salesforce's proprietary "flavor" of SQL.

And if you've used SOQL for a while, chances are you've encountered one or more of its limitations, which include:

• No arbitrary JOINs. You can traverse object relationships, but you can't freely join any two objects the way you can in SQL.
• No UNION or UNION ALL. You can't combine results from two separate queries into one result set.
• No CTEs (Common Table Expressions). You can't use WITH clauses to break complex logic into readable steps.
• No window functions. Things like RANK(), ROW_NUMBER(), or LAG() don't exist in SOQL.
• No CASE statements. Conditional logic in the SELECT clause isn't supported.
• No cross-platform queries. SOQL only sees Salesforce. It can't touch your Marketo data at the same time.

Having your Salesforce data queryable with actual SQL means you can overcome all of these restrictions. Here are a few simple examples of what you can do with SQL that you cannot do with SOQL.

Table of Contents

• Segmentation with Regex
• Lead and Contact Union
• First / Last Touch with Window Functions
• Cross-Platform Queries (e.g. Salesforce + Marketo)

Segmentation with Regex

Let's start with a simple one.

SOQL won't let you add conditional logic inside a SELECT. SQL does, with CASE. This is useful for on-the-fly segmentation or lead scoring without needing to create new fields in Salesforce.

SELECT COUNT(DISTINCT id) AS records,
       CASE 
          WHEN regexp_matches(title, '(?i)(VP|Chief|C-Suite|President)') THEN 'VP+'
          WHEN regexp_matches(title, '(?i)(Director|Lead)') THEN 'Director'
          ELSE 'Individual Contributor'
       END AS title_segment
FROM {{your_datachef_connection_id}}.lead
GROUP BY title_segment

Lead and Contact Union

Here, we combine unconverted Leads and Contacts into a single table, which is a core component of later examples.

  SELECT
      co.id,
      co.firstname,
      co.lastname,
      CASE 
          WHEN regexp_matches(title, '(?i)(VP|Chief|C-Suite|President)') THEN 'VP+'
          WHEN regexp_matches(title, '(?i)(Director|Lead)') THEN 'Director'
          ELSE 'Individual Contributor'
      END AS title_segment,
      co.email,
      a.name company
    FROM {{your_datachef_connection_id}}.contact co
    JOIN {{your_datachef_connection_id}}.account a ON a.id = co.accountid
    UNION
    SELECT
      id,
      firstname,
      lastname,
      CASE 
          WHEN regexp_matches(title, '(?i)(VP|Chief|C-Suite|President)') THEN 'VP+'
          WHEN regexp_matches(title, '(?i)(Director|Lead)') THEN 'Director'
          ELSE 'Individual Contributor'
      END AS title_segment,
      email,
      company
    FROM {{your_datachef_connection_id}}.lead
    WHERE NOT isdeleted AND NOT isconverted

We'll build on this in our next example...

First / Last Touch with Window Functions

Window functions let you calculate things across rows — like rankings, running totals, or picking the first/last value in a sequence — without collapsing your result into aggregates. SOQL has nothing equivalent.

First-touch and last-touch attribution are two of the most common questions in marketing ops: what originally brought this lead in? and what was the most recent interaction or the interaction prior to conversion?

Salesforce stores a single LeadSource field, but your Campaign Member data tells a more complete story.

WITH campaign_member_data AS (
    SELECT
        cm.leadorcontactid,
        cp.name campaign_name,
        cp.type campaign_type,
        cm.firstrespondeddate,
        ROW_NUMBER() OVER (PARTITION BY cm.leadorcontactid ORDER BY cm.firstrespondeddate ASC)  AS response_order_asc,
        ROW_NUMBER() OVER (PARTITION BY cm.leadorcontactid ORDER BY cm.firstrespondeddate DESC) AS response_order_desc
      FROM {{your_datachef_connection_id}}.campaignmember  cm
      JOIN {{your_datachef_connection_id}}.campaign cp ON cm.campaignid = cp.id
      WHERE cm.firstrespondeddate IS NOT NULL
)

, lead_contact_union AS (
  SELECT
      co.id,
      co.firstname,
      co.lastname,
      co.email,
      a.name company
    FROM {{your_datachef_connection_id}}.contact co
    JOIN {{your_datachef_connection_id}}.account a ON a.id = co.accountid
    UNION
    SELECT
      id,
      firstname,
      lastname,
      email,
      company
    FROM {{your_datachef_connection_id}}.lead
    WHERE NOT isdeleted AND NOT isconverted
)

SELECT 
  lcu.id,
  lcu.firstname,
  lcu.lastname,
  lcu.email,
  lcu.company,
  first_touch.campaign_name first_touch_campaign_name,
  first_touch.campaign_type first_touch_campaign_type,
  last_touch.campaign_name last_touch_campaign_name,
  last_touch.campaign_type last_touch_campaign_type
FROM lead_contact_union lcu
JOIN campaign_member_data first_touch ON first_touch.leadorcontactid = lcu.id AND first_touch.response_order_asc=1
JOIN campaign_member_data last_touch ON last_touch.leadorcontactid = lcu.id AND last_touch.response_order_desc=1

This gives you a row per Lead or Contact, showing exactly which campaign first got their attention and which one they responded to most recently.

Cross-Platform Queries (e.g. Salesforce + Marketo)

SOQL can only query Salesforce. So, if you've connected both Salesforce and a marketing platform like Marketo, you can perform advanced joins and performance analysis on combined data sets.

Here is an example where we join Contacts to Marketo Leads (People) and get the most recent Marketo activity date for each person.

SELECT 
    ml.id AS marketo_lead_id,
    sf_co.id AS salesforce_contact_id,
    act.activitydate AS last_activity_date
FROM {{your_marketo_connection_id}}.lead ml
JOIN {{your_salesforce_connection_id}}.contact sf_co USING (email)
JOIN (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY leadid ORDER BY activitydate DESC) AS rn
    FROM {{your_marketo_connection_id}}.activity
) act ON act.leadid = ml.id AND act.rn = 1

Want to explore your Salesforce data with SQL?

Watch a demo to see more interesting use cases or try Datachef free for 30 days.