Great post, Helen! Next time I'm logged into my website, I'll throw it into a "suggested reading" section of my "Every SQL Join..." post.
I stuck to "same people, different tables" joins because in Salesforce-land, most of what beginners do is beat their heads against the wall trying to combine customer data from different sources into a single table-shaped output, and I think a fair amount of my audience may come from Salesforce-land. (Also, I was having trouble thinking of meaningful examples that would be good for all my join types when sticking to proper PK-FK relationships.)
But I wanted to get around to also showing how joins could be used to look up more traditional PK-FK-connected details. Maybe now I don't have to ... I can just link to your examples. :-D
Thank you, that's great feedback. :)
Do you find SOQL to be very different to T-SQL over in Salesforce land? I'd love to read more about how they differ and any tips on making it easier to switch gears.
Yup, hugely different! You don’t get to DO joins of your own choosing at all. There’s a limited set of sub selects, “parent table lookups,” and semi-joins you can do between tables that already have PK-FK relationships, but there’s no “build whatever you want.” The closest thing, in-platform, is using their Java-like language to hand-join (iterate yourself imperatively) table dumps in memory. The seminal explanation of what you CAN do in pure SOQL is Jeff Douglas’s “SOQL, how I query thee, let me count the ways” (blog post)
Wow, sounds like a tough sell for a SQL/Excel analyst to get their head around. From what I understand it's what you use in Wave to do more than the basics.
That sounds more like SAQL, I think, which I haven’t played with because Wave (now “Einstein Analytics?”) costs extra and we don’t have it. But it DOES have a query language that lets you join at your leisure, I believe I recall from presentations on it. Looked way more like SQL, I believe. SOQL comes free as part of Salesforce for basic queries against the tables you create. SOQL is mostly custom-join-free, I suppose, both for simplicity and to keep non-DB-type amateurs from executing long-running cross-join queries on a server shared with other companies.
Hmmm I’m going to have to investigate the SOQL/SAQL a bit more. It sounds like I’ll be supporting analysts using one of them. It would be good to know which one!
We’re a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.