A lot of people ask me "why doesn't salesforce.com do outer joins". Some ask "I want to see Accounts without Contacts". Some ask "when are you going to do anti-joins". Some say "what are you talking about - anti-joins - I don't want to mix any anti-joins in with my data - it'll cause a huge explosion! I saw something about that anti-stuff on the Discovery Channel - it's dangerous!".
Normally, I try to help those last set of people get a glass of water and sit down, before talking about what the types of joins are.

For those of you who are database experts or who have worked with SQL in the past, you might want to skip the rest of the post. Of course, you never know - you might learn something. After all, salesforce.com is not a really a relational database...

The salesforce.com difference

salesforce.com is not really a relational database. There are those people that say, "It's got objects. Those are like tables. And they have fields. Those are like columns. It's a relational database". For those of you using the Salesforce API, you are saying, "But it's just like a relational database. There's SOQL. That proves it". If you said either of those two things to me, I would pooh-pooh you, because there are many differences. It is a database, but it's not a relational database. Those people who've written complex SOQL generators (like Sherman at Jaspersoft or Bill at forceAmp) would tell you it's very different.

• You can't join arbitrary columns
  • When you have columns in a database, you can join them. Although you may have told the database some of your schema (through relational integrity), you can still do things like "where customer.firstname=lead.firstname". You can't do that in salesforce.com. If the relationship is not there, then you can't join it. In fact, in SOQL, you don't specify any joins.
    
• We have defined cardinalities as part of the schema, and some of that is in the pages
  • When you defined your relationships whether they were 1:many, or many:1. There are only those two choices in salesforce.com - and there are two ways to set up these relationships - lookups, and master/detail relationships. Both come out as the same thing - the object where you create the field is at the many end of a 1:many relationship with the other object you selected.
• Whether there are records at the end of the join is decided by an administrator
  • When they set the lookup field as mandatory, or click the "opportunity needs products" checkbox, they decide that there is always going to be records at the end of the link. Otherwise, there might not be.

Different kinds of joins

Lets say we have two objects in salesforce, with the following records in the listviews (and we are selling plants).


(You might think "Carl isn't doing a good job - he's only getting $20", but really he's doing a great job in sales - he's getting 80% of that company's revenue!)

The "with" joins in salesforce.com (inner joins)

If you look at salesforce.com's report wizard, you'll see that there are many reports that contain something with something else. Accounts with Opportunities, Cases with Solutions, etc. There are lots of examples. These are inner joins (mostly), in database-speak. If I look at the two objects above, and do an "Accounts with Opportunities" report, I'd see:


And you'd say to yourself, where is startup.com?
Well, they don't have any opportunities. If I reported on Accounts, I'll see them all (in the listview above). But here I'm looking for Accounts with Opportunities. If they don't have Opportunities, they won't be there, because they don't come with. You could say that this was a "with at least 1 related" kind of report.

The "with and without" joins - outer joins

You may be asking if you could get all the Accounts, even if they didn't have Opportunities, and where they did have Opportunities, to get their details. And for you, the outer join was made. It's almost ideal for that case. If you were going to do a "with and without" report, you'd get:


...and there would be empty boxes in the list for opportunity data that isn't there.
There are some reports in salesforce.com like this - for instance, on Opportunities and revenue schedules today. But they are special, and you can't choose that anywhere.

But what would happen if we said we wanted to filter the list? To report on only those with Opportunity Amount > $1000? What would come back then? Does the empty box count at $0? Not a number? Something else? We'll talk about that another time.

Without - the anti-joins

Contrary to the alarmist view in the start of this story, anti-joins will not explode if mixed with real joins. In fact, they just mean something very different. They normally look for exceptions. They look for where there is no data - and you'd expect there to be some.

If I asked for "Accounts without Opportunities" then I'm asking for Accounts where there are none...


Well, that's just dandy. Now I can see which Accounts to call.
There are some of these reports in salesforce.com today - if you have Territory Management, there are reports to see which Accounts are not in any Territories - Accounts without Territories.

But wait, what if I say:
Show me accounts where there are no opportunities less than $1000
Is startup.com included then? Is that the same as doing a condition on the "with and without" report? More like a "without" report?

What does this mean for me?

Right now, there are a couple of ideas on the IdeaExchange:
Custom Report Types

This talks about being able include any objects in the report, provided you only want one from each set of related lists.

Exception Reporting (outer joins)

Here, things have gotten confusing. We've seen that the "outer joins" in the database world are not exception reporting. They don't look for exceptional data - for instance, an Account without any Opportunities. It just gets more rows back - the ones without matching Opportunities.

I haven't changed these two ideas because:

• They are really two different things to me - the Custom Report Types, and the "other join types".
• Outer joins and anti-joins are both things we don't do everywhere today.
• We can't split an idea (I would really like to split the second one in two) because we don't know which one people would have voted for.
  

But if you are looking at these ideas and voting for them, you should think about what you want. Maybe leave comments to this post. Start your own blog. Write on twitter. Start lifecasting yourself while you work on salesforce.com, a bit like justin.tv in front of your sales information.
Actually, commenting here or on directly IdeaExchange is better, because I might not find the other cases.

I need outer/anti/some-other-kind-not-described-here joins today!

Happily, thanks to the AppExchange, you can press a button and get them. There are a few of the vendors in the analytics category that already will let you do those things. Darren has already written about some of these partners and how to choose them: Your Analytic Requirements