3 Replies Latest reply: Nov 25, 2013 1:49 PM by Frank Kulash RSS

    Left Join Query Question

    user5415398

      Version 10.2.0.4.0

       

      I have a question on the expected behavior of the query below.

      When I run the query below with the constraint on t1.partid = 789,  I get the query result t2.Indicator showing "SPECIAL" as expected.

      However, if I remove the constraint, and return all orders and parts, for the "789" part, the Indicator column is null.

       

       

      select t1.orderid, t1.partid, t2.Indicator

      from Orders a left outer join

      (

      select partid, 'SPECIAL' as Indicator

      from vendors

      where vendorname like '%ABC%'

      ) t2

      on t1.partid = t2.partid

      where t1.partid = '789'

       

      I can address the issue with a case statement (below) or likely restructuring into a better statement. 

      But I'm just curious if this is expected or if there is some SQL rule being violated in the first example.

      I tried to search for this to see if it was already addressed but didn't have much luck.

       

      This works:

      select t1.orderid, t1.partid,

      case when t1.partid is not null then "SPECIAL" else null end as Indicator

      from Orders a left outer join

      (

      select partid, 'SPECIAL' as Indicator

      from vendors

      where vendorname like '%ABC%'

      ) t2

      on t1.partid = t2.partid

        • 1. Re: Left Join Query Question
          Frank Kulash

          Hi,

           

           

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.

          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.

          See the forum FAQ: https://forums.oracle.com/message/9362002

           

          user5415398 wrote:

           

          ... This works:

          select t1.orderid, t1.partid,

          case when t1.partid is not null then "SPECIAL" else null end as Indicator

          from Orders a left outer join

          (

          select partid, 'SPECIAL' as Indicator

          from vendors

          where vendorname like '%ABC%'

          ) t2

          on t1.partid = t2.partid

          Does that really work?  It looks like it would raise an error because table t1 is undefined.

           

          It might be more efficient to join directly to table2, rather than to a sub-query.  (Of course, that's just avoiding the question, not answering it.  It would be best to figure out why you're getting the current results, even if you don't need to.)

          • 2. Re: Left Join Query Question
            user5415398

            Sorry, it's been a while since I posted and should have read the rules.  And I didn't properly reference the alias.  So Post #1 was bad. 

             

            When I mockup a small set of data (shown below), I don't get the error.  The original query actually joins to a few other (seemingly) irrelevant tables and I tried to simplify it here  So I guess if I can't replicate it, then there might not be much assistance that can be provided.

             

            This was more of a curiousity than anything else to see if perhaps someone came across this before.

             

            For what it's worth:

             

            create table t1 (orderid number,

                         partid varchar2(20)

                    )

             

            create table t2

             

                    (vendorid varchar2(20),

                     partid varchar2(20)

                           )

             

            insert into t1 values(1, '123');

            insert into t1 values(2, '456');

            insert into t1 values(3, '789');

             

            insert into t2 values ('ABC','789');

            insert into t2 values ('DEF','123');

            insert into t2 values ('EFG','456');

            insert into t2 values ('ABC','7891');

            insert into t2 values ('DEF','1231');

            insert into t2 values ('EFG','4561');

             

            select t1.orderid, t1.partid, t2.Indicator

            from t1 left outer join

            (

            select partid, 'SPECIAL' as Indicator

            from t2

            where vendorid like '%ABC%'

            ) t2

            on t1.partid = t2.partid

             

            the query that gives unexpected behavior is (although I can't replicate on this simplified version):

             

            select t1.orderid, t1.partid, t2.Indicator

            from t1 left outer join

            (

            select partid, 'SPECIAL' as Indicator

            from t2

            where vendorid like '%ABC%'

            ) t2

            on t1.partid = t2.partid

            • 3. Re: Left Join Query Question
              Frank Kulash

              Hi,

               

              If you really want to know why this is happening, zero in on exactly what is causing it.  The simplified problem you posted does not have the unexpected behavior, so add a little bit more of the complexity, test again, and repeat, until you do get the unexpected results.  Then post the last version of the query that got the expected results, and the version that got the unexpected results, along with the CREATE TABLE and INSERT statements for anything needed to run either version.

               

              Do any of the tables have deferrable foreign key constraints?  I've seen bugs where the optimizer assumed the constraint had been enforced when it was still deferred.