This setting specifies how Stonefield Query handles a filter condition on the unfavored table of an outer join by default. In SQL, when you specify an outer join between tables, you're indicating that you want all records from the favored table whether there are matches in the unfavored table or not. If you add a filter condition on the unfavored table, SQL treats it like an inner join: now you only get records from the favored table that have matches in the unfavored table.

Because that behavior is unexpected, previous versions of Stonefield Query moved the filter condition to the JOIN clause so the unfavored table is filtered but unmatched records in the favored table are still included. However, there are times when the user doesn't want that behavior; they want unmatched records eliminated even though it's an outer join. So, you now have a choice about what to do with a filter condition on the unfavored table.

The choices are:

  • WHERE clause: puts the filter condition in the WHERE clause of the SQL statement:

      select ParentTable.Field1, ChildTable.Field2
          from ParentTable
          left outer join ChildTable on ParentKey.ID=ChildTable.ParentID
          where ChildTable.Field3 = 'SomeValue'
    
  • JOIN clause: puts the filter condition in the JOIN clause:

      select ParentTable.Field1, ChildTable.Field2
          from ParentTable
          left outer join ChildTable on ParentKey.ID=ChildTable.ParentID
              and ChildTable.Field3 = 'SomeValue'
    
  • JOIN clause if unfavored parent table, WHERE clause otherwise: puts the filter condition in the JOIN clause if the unfavored table is the parent or in the WHERE clause if it's the child table.

  • JOIN clause if unfavored child table, WHERE clause otherwise: puts the filter condition in the JOIN clause if the unfavored table is the child or in the WHERE clause if it's the parent table.

  • JOIN clause if unfavored parent table, subquery otherwise: puts the filter condition in the JOIN clause if the unfavored table is the parent or in the WHERE clause of a subquery if it's the child table.

  • JOIN clause if unfavored child table, subquery otherwise: puts the filter condition in the JOIN clause if the unfavored table is the child or in the WHERE clause of a subquery if it's the parent table.

See Also

Configuration Settings | Relation Properties