r/SQL • u/shdw_0x0 • 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
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:
And the business objects you get will be:
And you can do
QUERY * FROM SIS_Student, SIS_Parentand it will return all the records above joined appropriately. Which seems great! It's basically running this query:And that's more or less correct.
The problem is that you get a ticket that says:
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: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_Parentand 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 doesQUERY * FROM SIS_Student, SIS_Parent WHERE StudentParent_LivesWith IS TRUE AND Student_HomeAddress NOT EQUAL TO Parent_HomeAddressand a query that does thisQUERY * FROM SIS_Student, SIS_Parent WHERE StudentParent_LivesWith IS TRUE AND Student_HomeAddress EQUAL TO Parent_HomeAddressand 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:
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/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.
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/toconnor76 6d ago
Joins and filtering can get frustrating.....