5 Replies Latest reply: Feb 8, 2013 11:35 AM by rp0428 RSS

    Restricting data from a left outer joined table.

    sufiyan
      Hi all,

      I have a simple question for the gurus here. I have a query something like this.

      Select

      Table1.Account,
      Table1.Column2,
      Table2.Account2,
      Table2.ColumnB,

      FROM
      Table1
      Left Outer Join Table2 ON
      table1.account = table2.account2



      My question is ColumnB from Table2 contains only two values: NEW and OLD.
      I want to restrict data to 'OLD' only. Someone told me that because my table2 is outer joined, i will not be able to restrict my data? Is that true?

      I am sorry for if its a stupid question but i am confused. Thanks a lot in advance.
        • 1. Re: Restricting data from a left outer joined table.
          Solomon Yakobson
          Z KHAN wrote:
          Someone told me that because my table2 is outer joined, i will not be able to restrict my data? Is that true?
          Not true:
          with Table1 as (
                          select 1 Account,100 Column2 from dual union all
                          select 2,200 from dual union all
                          select 3,300 from dual union all
                          select 4,400 from dual
                         ),
               Table2 as (
                          select 1 Account2,'OLD' ColumnB from dual union all
                          select 1 Account2,'NEW' ColumnB from dual union all
                          select 2,'OLD' from dual union all
                          select 3,'NEW' from dual
                         )
          select  Table1.Account,
                  Table1.Column2,
                  Table2.Account2,
                  Table2.ColumnB
            from      Table1
                  Left Outer Join Table2
                   ON (
                           table1.account = table2.account2
                       and
                           Table2.ColumnB = 'OLD'
                      )
          /
          
             ACCOUNT    COLUMN2   ACCOUNT2 COL
          ---------- ---------- ---------- ---
                   1        100          1 OLD
                   2        200          2 OLD
                   4        400
                   3        300
          
          SQL>
          SY.
          • 2. Re: Restricting data from a left outer joined table.
            rp0428
            >
            My question is ColumnB from Table2 contains only two values: NEW and OLD.
            I want to restrict data to 'OLD' only. Someone told me that because my table2 is outer joined, i will not be able to restrict my data? Is that true?
            >
            No - it is not true. Perhaps that 'someone' does not understand what an 'outer join' is or does.

            A query like yours is of the form
            Select  . . . FROM Table1 Left Outer Join Table2 ON . . .
            That 'left outer join' will return ALL rows from TABLE1 that meet the filter conditions. The JOIN says NOTHING about table2.

            See the SQL Language doc
            http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
            >
            Outer Joins An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

            •To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.
            • 3. Re: Restricting data from a left outer joined table.
              chris227
              To clear up things,we can look for at last a bit of truth of the statement made, and there is

              You can't restrict the rows of the left table in a left outer join in the join conditions of the ansii-join-syntax:
              with Table1 as (
                              select 1 Account,100 Column2 from dual union all
                              select 2,200 from dual union all
                              select 3,300 from dual union all
                              select 4,400 from dual
                             ),
                   Table2 as (
                              select 1 Account2,'OLD' ColumnB from dual union all
                              select 2,'OLD' from dual union all
                              select 3,'NEW' from dual
                             )
              select  Table1.Account,
                      Table1.Column2,
                      Table2.Account2,
                      Table2.ColumnB
                from      Table1
                      Left Outer Join Table2
                       ON (
                               table1.account = table2.account2
                           and
                               Table1.Column2 = 100
                          )
              order by account
              
              ACCOUNT     COLUMN2     ACCOUNT2     COLUMNB
              1     100     1     OLD
              2     200          
              3     300          
              4     400          
              
              you have to do this in the where condition instead
              
              with Table1 as (
                              select 1 Account,100 Column2 from dual union all
                              select 2,200 from dual union all
                              select 3,300 from dual union all
                              select 4,400 from dual
                             ),
                   Table2 as (
                              select 1 Account2,'OLD' ColumnB from dual union all
                              select 2,'OLD' from dual union all
                              select 3,'NEW' from dual
                             )
              select  Table1.Account,
                      Table1.Column2,
                      Table2.Account2,
                      Table2.ColumnB
                from      Table1
                      Left Outer Join Table2
                       ON (
                               table1.account = table2.account2
                           
                          )
              where
                  Table1.Column2 = 100            
              order by account
              
              ACCOUNT     COLUMN2     ACCOUNT2     COLUMNB
              1     100     1     OLD
              Edited by: chris227 on 28.01.2013 00:51
              • 4. Re: Restricting data from a left outer joined table.
                sufiyan
                Hi guys, sorry for a late reply. Thank you very much for clarification. Thanks
                • 5. Re: Restricting data from a left outer joined table.
                  rp0428
                  Please mark the question ANSWERED if it has been.