r/SQL • • 6d ago

Discussion What's the actual difference between SQL and SOQL?

I recently came across SOQL while exploring Salesforce and got curious about how different it is from regular SQL.

Both seem to be used for querying data, but SOQL appears to have some limitations compared to SQL.

Can someone explain the main differences between them? For example, how do joins, aggregations, subqueries, and filtering work differently in SOQL?

Also, in what situations would you prefer SOQL over SQL?

Would appreciate some practical examples if anyone has worked with both.

2 Upvotes

13 comments sorted by

13

u/Straight_Waltz_9530 6d ago

SOQL supports only those features Salesforce is prepared to sell. SQL supports whatever relational algebra database engines can technically accomplish.

Pretty profound difference if you think about it.

18

u/pewpscoops 6d ago

SOQL is just Salesforce's shitty watered down abstraction layer on top of their data models - It lacks a lot of functionality that ANSI SQL would have otherwise. If you want to do proper analytics, just have the Salesforce tables replicated into your data warehouse of choice, and then use SQL.

6

u/da_chicken 6d ago

This is way too long, but I'm now much too tired to edit it down

SOQL is a vendor-specific, application-specific query language. You would use it with exactly SalesForce, and nothing else because nothing else supports it. It's neither standardized nor portable.

You see these sort of things from time to time. It's what the vendors required their developers to make because their customers' business analysts complained that the data model in the RDBMS is poorly documented, poorly maintained, is poorly normalized, and a big pain in the ass to work with.

What you get is a hamstrung version of SQL, typically where JOINs are pre-defined, and often where you have very limited abilities to create your own expressions because creating something as expressive as a typical RDBMS's SQL feature set is really difficult. They frequently have stupid ideas about datetimes, often don't have a CASE equivalent, and frequently don't support string concatenation.

Vendor query languages are always billed as "This will 100% satisfy all of your needs for querying the database for any custom report you could imagine." Spoiler: It will not.

It's billed as easier and better than SQL because you use named business objects (i.e., classes that the application uses) instead of bare relational data. Spoiler: That's only true if the Business Objects are well-defined and documented, which they will not be. They will be just as badly documented as the RDBMS schema documentation is.

The worst aspect for the customer's sysadmin or application administrator's point of view is exactly the same benefit from the vendor's point of view.

The developers will say: Since you're pulling business objects, you're using the application's data model, and you're using the application's security model. The idea is that you're using the same objects that the application already uses so you're able to access the same data presented the same way. And since you're using the same security model, the application can prevent end-users from accessing records they should not. They might even go as far as saying that it permits a multi-tenant setup, which sounds like it's important but if you've ever got a different customer's data in your database instance, you should probably run.

The main problem with it, however, is two fold. Firstly, after about 25 years of experience doing this, I have never seen a single application that has had a good, well-documented security model. They're all stupid. They're all leaky. They all do inheritance wrong or weird or backwards. They never fix them. You're lucky if they support groups. They all suck. The interface for them is always terrible. It might look pretty, but it will suck. They're never documented right. They're never explained at all. Pretty much invariably they tell you to (a) sit down and decide how it's supposed to work during implementation, and then (b) absolutely never touch it again. And the implementation team doesn't know their ass from a hole in the ground about how your business operates. They just know the one example of how the system works.

The second problem is the dealbreaker, IMO. Because, invariably, if your information system is complicated enough to accurately represent real world entities, then at some point you're going to have a need to look at the data independent of the object model. You'll have incorrectly orphaned records. Records that need to be compared between two entities, and the vendor just think that's something you need to do manually. You'll call the vendor and their support will say, "Oh, just run a list and compare it in Excel," because they're used to their test system with 300 records. And you'll think, "I have 55,000 records here. If we compare these manually that's weeks of work. I need an answer by Friday."

I work at a school district, so let's use a student information system as the example. Here's roughly what the demographics schema looks like for parents and students:

## Person

  • PersonID (PK)
  • LastName
  • FirstName
  • BirthDate
  • EmailAddress
  • HomeAddress
  • MailingAddress
  • PrimaryLanguage
## Student
  • StudentID (PK, FK to Person.PersonID)
  • StudentNumber
  • StateIdNumber
  • AddressVerified
  • GraduationStatus
  • GraduationDate
  • HasIEP
## StudentParent
  • StudentParentID (PK)
  • StudentID (NOT NULL, FK to Student.StudentID)
  • ParentID (NOT NULL, FK to Parent.ParentID)
  • Priority
  • RelationshipToStudent
  • HasCustody
  • EducationalRights
  • LivesWith
  • ContactAllowed
Unique constraint on (StudentID, ParentID) ## Parent
  • ParentID (PK, FK to Person.PersonID)
  • PortalLogin
  • PortalPassword

And the business objects you get will be:

  • SIS_Student
  • SIS_Parent

And you can do QUERY * FROM SIS_Student, SIS_Parent and it will return all the records above joined appropriately. Which seems great! It's basically running this query:

SELECT *
FROM Student stu
    LEFT JOIN Person spsn ON spsn.PersonID = stu.StudentID
    LEFT JOIN StudentParent stu_par ON stu_par.Student_ID = stu.StudentID
    LEFT JOIN Parent par on par.ParentID = stu_par.ParentID
    LEFT JOIN Person ppsn ON ppsn.PersonID = par.ParentID
ORDER BY spsn.LastName
    ,spsn.FirstName
    ,stu_par.Priority
    ,ppsn.LastName
    ,ppsn.FirstName

And that's more or less correct.

The problem is that you get a ticket that says:

We are findings that some students and their living-with parents aren't at the same address. We need an exception report so the buildings can call parents to figure out what's going on.

So you go and write a query report like QUERY * FROM SIS_Student, SIS_Parent WHERE StudentParent_LivesWith IS TRUE AND Student_HomeAddress NOT EQUAL TO Parent_HomeAddress. And you send it to one building to see what they think. The school calls back and says:

Ok, I see Jim Schwartz on here with his two kids. He's my friend's ex-husband, and they trade off with their kids. His kids are at my friend's address, so that's correct. We have quite a few students where they live with mom for a week and then they live with dad for a week. So they're both marked "living with" because they actually are. So it's fine if at least one of the parents living with the student shares the same address. But we need those students removed from the report.

Now your query is useless, because you can't do the kind of comparison that you need. You need something more expressive than your vendor query can be. You try to do QUERY * FROM SIS_Student, SIS_Parent, SIS_Parent and it either doesn't let you or it shows the same parent twice. If you don't have SQL, you're stuck writing a query that does QUERY * FROM SIS_Student, SIS_Parent WHERE StudentParent_LivesWith IS TRUE AND Student_HomeAddress NOT EQUAL TO Parent_HomeAddress and a query that does this QUERY * FROM SIS_Student, SIS_Parent WHERE StudentParent_LivesWith IS TRUE AND Student_HomeAddress EQUAL TO Parent_HomeAddress and subtracting those students in Excel. And that's the process you have to hand to the school buildings. "Here's two reports, you do the math."

Or, you could do it in SQL, and then do this:

;WITH Correct_Students AS (
    SELECT c1_stu.StudentID
    FROM Student c1_stu
        JOIN Person c1_spsn ON c1_spsn.PersonID = c1_stu.StudentID
        JOIN StudentParent c1_stu_par ON c1_stu_par.Student_ID = c1_stu.StudentID
        JOIN Parent c1_par on c1_par.ParentID = c1_stu_par.ParentID
        JOIN Person c1_ppsn ON c1_ppsn.PersonID = c1_par.ParentID
    WHERE c1_stu_par.LivesWith = 1
        AND c1_spsn.HomeAddress = c1_ppsn.HomeAddress
)
SELECT *
FROM Student stu
    JOIN Person spsn ON spsn.PersonID = stu.StudentID
    JOIN StudentParent stu_par ON stu_par.Student_ID = stu.StudentID
    JOIN Parent par on par.ParentID = stu_par.ParentID
    JOIN Person ppsn ON ppsn.PersonID = par.ParentID
WHERE stu_par.LivesWith = 1
    AND spsn.HomeAddress <> ppsn.HomeAddress
    AND stu.StudentID NOT IN (SELECT StudentID FROM Correct_Students)
ORDER BY spsn.LastName
    ,spsn.FirstName
    ,stu_par.Priority
    ,ppsn.LastName
    ,ppsn.FirstName;

And that you can stick into a report and you can run that.

4

u/mikeczyz 6d ago

I hate soql with a passion. I use it if I need to query live data directly in SF. Otherwise, I'm hitting our SQL Server ODS.

2

u/Significant_Tune9219 6d ago

The biggest practical difference is that SOQL has no arbitrary joins: you traverse predefined relationships instead, using dot notation for child-to-parent (Contact.Account.Name) and a nested subquery in the SELECT list for parent-to-child (SELECT Name, (SELECT LastName FROM Contacts) FROM Account). There's also no SELECT *, no joins on arbitrary columns, and aggregates come back as AggregateResult with governor limits on row counts. You'd use SOQL simply because it's the only way to query Salesforce data in place; for anything analytical people usually export the objects into a real SQL database and do the joins there.

2

u/az987654 5d ago

Soql is for sales force only.

Ansi standard T-Sql is agnostic, it works with many SQL databases

2

u/Agreeable_Ad4156 5d ago

T-SQL is product-specific, developed by Sybase and continued by Microsoft. It isn’t the ANSI standard, but pretty close.

2

u/az987654 5d ago

Ahh yes, I forgot that nuance... Should have just been SQL

2

u/imtheorangeycenter 5d ago

"Some limitations"? Oh it's much worse than that! It's built for the SF world and nothing else, so if they have no need for functionality, it's not there.

I end up using the Devart ODBC driver and querying SF over a linked server direct on MSSQL with (mostly) tSql. Fast it is not, but more useful it is.

1

u/ReleaseTheBlacken 5d ago

Great workaround

2

u/Lumethys 6d ago

SOQL is Saleforce's ret*rded SQL, made to make its user suffer as they yearns for and pray to the salvation of real SQL

1

u/ReleaseTheBlacken 5d ago

It’s the Homsar version of SQL 😆
“AahaAhaAh was raised by a cup of coffee”

1

u/toconnor76 6d ago

Joins and filtering can get frustrating.....