![\"Join](\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2026\/08\/join-to-one-post-on-bsky.png\")
<\/a><\/figure>\n\n\n\nThis led to some discussions in private channels. <\/p>\n\n\n\n
Back then, I hadn’t made up my mind regarding the usefulness of this feature, but the fact that a join uses outer-join semantics by default irritated me from the very first moment.<\/p>\n\n\n\n
In the meantime, I believe that this feature is useful for various use cases. I’m not going to talk about that in this blog post. However, the longer I thought about the implicit outer join in join-to-one, the more it irritated me.<\/p>\n\n\n\n
Join-To-One Will Make It Into the SQL Standard<\/h2>\n\n\n\n
According to Peter Eisentraut’s blog post<\/a>, this feature has been accepted into the next SQL standard and is now part of its working draft. I wonder whether they also discussed the default join behaviour of a join-to-one and what the consensus was. <\/p>\n\n\n\n Anyway, since version 26.2, this feature is part of Oracle AI Database. Changing the behaviour at that stage is only feasible with a database parameter similar to group_by_position_enabled<\/a> to keep backward compatibility. I doubt that something like this will happen.<\/p>\n\n\n\n It is the first time an outer join has become the default. For every other join variant since ANSI SQL-86, an inner join has been the default. We are used to that.<\/p>\n\n\n\n Changing such a long-established convention is not necessarily a bad thing. However, there should be a convincing reason for doing so.<\/p>\n\n\n\n The SQL Language Reference<\/a> provides this reason:<\/p>\n\n\n\n “The database chose this default because So, the intention is to avoid accidental filtering. That sounds reasonable. But is silently preserving rows really better than silently filtering them?<\/p>\n\n\n\n In both cases, the omitted join type affects the result. An implicit inner join may remove rows. An implicit outer join may introduce null values that have to be considered in subsequent expressions and predicates.<\/p>\n\n\n\n In any case, the join-to-one clause relies on integrity constraints. These constraints are already used to derive the join condition. So why not consider them fully and choose an inner join for mandatory foreign keys and an outer join for optional foreign keys?<\/p>\n\n\n\n Of course, this would make the join type depend on the schema definition. A change to the optionality of a foreign key could then change the semantics of an existing query. That would have been a very good reason not to choose a fixed default join type, right?<\/p>\n\n\n\n However, while the optimiser could deal with a dynamically determined join type, the query would be more challenging to read and understand. We would need to check the current optionality of each foreign key involved to determine whether nulls need to be handled.<\/p>\n\n\n\n Ouch. This would be cumbersome without implementing an additional concept that forces us to handle null values in SQL expressions and predicates (something similar to JSpecify<\/a>). Therefore, it’s probably better not to derive the join type automatically from a foreign key’s optionality. Being explicit has its value.<\/p>\n\n\n\n Furthermore, an outer join limits the optimiser’s solution space and can affect performance, as Andrej Pashchenko demonstrated for join-to-one in this blog post<\/a>.<\/p>\n\n\n\n These performance aspects may be addressed in upcoming versions. But the unfamiliar implicit default join strategy remains.<\/p>\n\n\n\n Let’s compare the following two queries:<\/p>\n\n\n\nWhat Is the Problem?<\/h2>\n\n\n\n
\n
INNER JOIN<\/code>s often filter rows unintentionally.”<\/p>\n<\/blockquote>\n\n\n\nAn Example<\/h2>\n\n\n\n