Are there any functions / keywords / syntax in SOQL queries that does NOT have an equivalent operation in SQL?

Basically, does there exist a SOQL query that you couldn't convert directly into a SQL query?

CodePudding user response：

Some differences of SOQL:

No SELECT *

No views

SOQL read-only

Limited indexes

Object-relational mapping is automatic

Schema changes protected

CodePudding user response：

Weird question, why do you ask? And which SQL you mean exactly, Oracle, SQL Server flavour, Maria DB or what?

I'd say you'll have hard time

• mapping SELECT Account.Owner.Manager.Profile.Name FROM Opportunity into "normal" joins
• replicating TOLABEL() (translate picklist values on the fly)
• replicating anything SF-specific like WITH (say knowledge base's data categories) or USING SCOPE (you can pull "my accounts" but can you pull "my team's accounts"? "My territory's accounts"? Without an orgy of joins)
• doing joins over mutant polymorphic fields like Task.WhatId or ContentDocumentLink.LinkedEntityId
• doing any kind of SOSL, especially if org uses synonyms
• converting currencies on the fly
• doing things like FISCAL_YEAR() without orgy of joins to Period table
• replicating any geolocation-related queries (accounts up to 10 km away from me) without knowing exactly what GIS (or whatever) SF uses
• doing soft deletes (or however Recycle Bin really works) without impact on performance. I mean if records go to another table - columns are identical and join/view happens magically when you query ALL ROWS?
• doing any Person Account stuff, silently querying and updating effectively 2 tables (as materialised view maybe?)