This discussion is archived
5 Replies Latest reply: Feb 8, 2013 9:35 AM by rp0428 RSS

Restricting data from a left outer joined table.

sufiyan Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Please mark the question ANSWERED if it has been.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points