I'd like clarifications about how Oracle handles left joins that contains more than one join restriction. See the query below:

  , category.parent_category_id
  , lpad(' ', 4 * (category.c_level - 1)) || nvl(context.category_name, category.category_name) as name
  , nvl(context.sequence_num, category.sequence) as sequence
  , nvl(context.is_suppressed, 'N') as is_suppressed
  , decode(category.syndicate_url, null, 'N', 'Y') as is_goto
  , category.start_date
  , category.end_date
  , decode(category.is_leaf, 0, 'N', 'Y') as has_children
  , category.c_level
from (
    , parent_category_id
    , category_name
    , sequence
    , start_date
    , end_date
    , syndicate_url
    , connect_by_isleaf as is_leaf
    , level as c_level
  start with
    category_id = 118
    connect by 
      parent_category_id = prior category_id
      and level <= 3
) category
  inner join category_destination_channel channel on channel.category_id = category.category_id
    and channel.destination_channel_id = 1
    and channel.publish_flag = 'Y'
  left join contextual_category context on context.category_id = category.category_id
    --and context.is_suppressed <> 'Y'
    and context.context_type = 'DESKTOP'
  category.start_date <= sysdate
  and category.end_date >= sysdate
  and nvl(context.is_suppressed, 'N') <> 'Y'

In my database there are two categories that have corresponding contextual_category records with is_suppressed values equal to 'Y'.

When I run the query as provided (note that and context.is_suppressed <> 'Y' under the left join is commented out and an equivalent condition is provided in the where clause) the result is correct, i.e. those two categories that are suppressed do not show up.

If I uncomment the left join condition and comment out the where condition (and nvl(context.is_suppressed, 'N') <> 'Y') then the result set contains those two categories that are suppressed. Worse, the value of is_suppressed in the result set for those categories is 'N'.

What is the cause for this inconsistent behavior? Is it not a good ideia to, when using left join, move the restrictions from where clause to the join clause (since in the where clause I would need to use something like nvl function to handle the null cases)?

Thank you very much!


Sorry about the huge query... Basically I have two tables: category and contextual_category. The first contains the default data for categories: id, parent id, sequence number, name, start and end dates. contextual_category contains "overrides" of the default data for different context. For example:

id parent_id    name         sequence    start_date    end_date
1  null         Root         100         2015-9-1      2015-9-30
2  1            Child        100         2015-9-1      2015-9-30

id    category_id    name     sequence  is_suppressed  context
100   2              CDesk    300       'Y'            'DESKTOP'
101   2              CMobi    13        'N'            'MOBILE'

Basically what I want is to use values on contextual_category (for name, sequence and is_suppressed) when the record exists, else use the "default" values defined in category.

    (...) category c
    inner join category_destination_channel (...)
    left join contextual_category cc
        on cc.category_id = c.category_id
        and cc.context_type = 'DESKTOP'
        and cc.is_suppressed <> 'Y'

See that a category can or cannot have one or more corresponding records in contextual_category (one record for each "context"). With the query above I'd like to retrieve active (not suppressed) categories for the DESKTOP context. The result I expected was only Root but it returned both Root and CDesk, even tough this category is suppressed in the DESKTOP context.

  • It sounds to me like you're describing the standard behavior of a left join according to the SQL standard. But it's hard to say for sure because you haven't provided a reproducible test case that we can run. I'd guess that you could reproduce the issue with 2 tables each having a couple columns and a handful of rows of data and a 3 or 4 line SQL statement. If you can, then it's probably much easier to explain and the explanation will be much clearer. – Justin Cave Sep 24 '15 at 21:00
  • You seem to need INNER JOIN, not LEFT here – a1ex07 Sep 24 '15 at 21:26
  • Logically the JOIN is processed first and the resulting set is filtered by the WHERE condition. Now when you put the condition in ON it's part of the join-condition, and due to the outer join the row is still returned, but NULLed. There's a rule of thumb for search-conditions in Outer Joins: Usually a condition on the Outer table is put in WHERE, a condition on the Inner table is put in ON. – dnoeth Sep 25 '15 at 6:36
  • Can you show your expected output? When I look at your sample data and your query, I see you returning both rows from category but the cc fields all blank because the left join failed on those conditions. – Hambone Sep 28 '15 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.