[Tips] Inner join: just don't use it

[image unavailable: Picture 3]

8 years later and Servoy is still populating the relation join type with "inner join" as the default. Even Servoy's Wiki says to use left outer join instead:

[image unavailable: Picture 4]
This is a bad thing because there is one hidden side effect of inner joins that can lead to a number of unexpected results. Namely, if you do a sort on the related records, parent records with no related records are removed from their foundset.

Examples:

1 Search and sort removes records

Let's say a search of companies.name = %Factory% returns 10 records. Then you do a a sort on companies_to_contacts.name_last and 8 records are now showing instead of 10. The two records that go missing don't have any companies_to_contacts records.

2 Search doesn't return expected records

Let's say a search of companies.name = %Factory% and companies_to_contacts.name_last = %Smith% (an OR search) returns 2 records less than you're expecting. The two records that should have been returned by the first match were removed from the result set because they didn't have any child records as defined by the inner join on the second match.

3 Extended search removes records

Doing an extended search (ie, foundset.search(false, false)) using related search criteria removes valid records from the starting foundset. Same reason as #2. This is a bug in my opinion as an extended search should never remove records from the foundset you are starting from.

Summary

The inner join unexpected behaviors are even more noticeable now that we have the ability to add default sorts to relations. So there is a good chance just about anyone now will stumble across something they don't understand.

Unless you have a good case for using inner joins, don't!

Comments (5)

Servoy Stuff 2010-07-30

One good reason for using inner join is performance: In most case it is faster than left outer join, so if you don't need to search on related fields or sort on them, this will give you quicker results. And what you see as a bug is rather how SQL is dealing with joins so I would say this is expected behavior.

john allen 2010-07-31

What happens when using a left outer join - say company_to_contacts - and for a particular company you are testing the validity of that relationship (if(company_to_contact) or if(!company_to_contact))? I've never tested it but I've also always used inner joins unless I've had a reason to use an outer join (too many years of writing SQL I guess...)

David Workman 2010-08-01

In general Servoy usage, tuning SQL performance with the inner/outer join switch on relations is negligible compared to all the other factors involved. Database vendor, the SQL that Servoy generates (limit clauses on everything to start with), indexing, etc. In a complex reporting query I could see it coming into play - but then I'd be writing those queries by hand anyways. The reason I say it's a bug is because Servoy is inconsistent with generating inner join statements - only when there is a sort on the inner join. If there isn't a sort, Servoy issues an outer join statement. So maybe instead it's a bug that Servoy doesn't always generate an inner join instead of the other way around. In any case, the result is that we end up with confusion when the parent foundset of an inner join relation is a different size depending on whether or not it is sorted by a column through the inner join relation. John: testing relations is the same with inner and outer join relations.

Ryan Parrish 2010-08-08

Now, I disagree with point number 2. It seems completely counterintuitive to me that the other two companies that don't have a "Smith" contact would be returned. If I want to find all the companies that have someone named "Smith" and perform an operation on them, at each iteration of the loop I would have to check hasRecords(companies_to_contacts). Otherwise I would be doing this "Smith" specific operation on companies without a "Smith". I think I have the same mindset as John, I'm writing the SQL in my head when I search and at least 98% of the SQL queries I write are inner joins. Is it the default behavior of FM to return those other two rows? Perhaps we have a different way of thinking about relational queries?

Rob Gansevles 2010-08-23

David, Regarding point 3, which version of Servoy do you use? The bug you describe has been fixed in Servoy 5.0 which is released almost a year ago. Rob