Oracle outer join "entity"

Question

How can you refer to the "combined entity" created from an outer join within your query? Specifically how can you replace the "??" in the query below:

SELECT TableA.x, 
       ??.y --How do you select from the combined entity?
  FROM TableA, 
       TableB buys 
FULL OUTER JOIN TableB sells ON buys.run_id = sells.run_id 
                            AND buys.specie_id = sells.specie_id 
                            AND buys.account_id = sells.account_id
WHERE TableA.id = ?? 
  AND -- want to join this with the "combined entity" resulting from the outer join
      buys.buy = 'Y' 
  AND -- Is this valid or does this have to be within the Outer join statement?
      sells.buy = 'N';

ypercubeᵀᴹ · Answer 1 · 2011-06-21T15:15:18.993

select TableA.x, fullTable.y 
from TableA 
  INNER JOIN
    ( SELECT y
        FROM TableB buys
          full outer join TableB sells
            on buys.run_id = sells.run_id
            and buys.specie_id = sells.specie_id
            and buys.account_id = sells.account_id
            AND buys.buy = 'Y' AND sells.buy = 'N'    
    ) AS fullTable
    ON TableA.id = fullTable.y

The condition can be in the final WHERE or ON like this but it essentially cancels the full join:

select TableA.x, fullTable.y 
from TableA 
  INNER JOIN
    ( SELECT y
           , buys.buy AS buysBuy             --- fields here so they can be
           , sells.buy AS sellsBuy           --- used in the final WHERE or ON
        FROM TableB buys
          full outer join TableB sells
            on buys.run_id = sells.run_id
            and buys.specie_id = sells.specie_id
            and buys.account_id = sells.account_id                
    ) AS fullTable
    ON TableA.id = fullTable.y
    AND buysBuy = 'Y' AND sellsBuy = 'N'    --- the condition here

I get `ORA-00905: missing keyword` on the `AS fullTable` part of the query. — Marcus Leon, Jun 21 '11 at 14:58
@Marcus: I suppose you have removed the comment: `--- this condition here`. I can't see any error otherwise. — ypercubeᵀᴹ, Jun 21 '11 at 15:16

score 2 · Accepted Answer · answered Jun 21 '11 at 14:54

Presumably what you're trying to ask is how to refer to the columns used in the join in other parts of the query. There is no joined entity per se; you still have to refer to the columns from the tables (in this case "buys" or "sells", it just happens that the columns used in the join will have the same value for both tables.

If you want whichever is not null (since this is a full outer join), you can use coalesce or nvl to find find the non-null value:

SELECT TableA.x, 
       nvl(buys.run_id,sells.run_id) as run_id,
       ...

Tony Andrews · Answer 3 · 2011-06-21T16:07:30.767

1

You need the sells.buy = 'N' and buys.buy = 'Y' predicates inside the outer join.

SELECT TableA.x, 
       ??.y --How do you select from the combined entity?
  FROM TableA, 
INNER JOIN TableB buys ON <whatever>
                      AND buys.buy = 'Y' 
FULL OUTER JOIN TableB sells ON buys.run_id = sells.run_id 
                            AND buys.specie_id = sells.specie_id 
                            AND buys.account_id = sells.account_id
                            AND sells.buy = 'N'

As for ??.y, you need to specify whichever table you want it from - TableA, buy or sell.

edited Jun 21 '11 at 16:07

answered Jun 21 '11 at 14:56

Tony Andrews

129,880
21
220
259

@Tony: I don't understand why you say it matters for `sells.buy = 'N'` but not for `buys.buy = 'Y'` – ypercubeᵀᴹ Jun 21 '11 at 15:03
Because with outer joins the intent is to allow it to return inner table rows that have no corresponding outer table row. As soon as you add "where outer.col = value" to the query, you no longer have an outer join - you have forced it back to an inner join (because any nulls will not match the predicate). – Tony Andrews Jun 21 '11 at 15:09
@Tony: I agree on that, teh join is cancelled. But I don't understand the differentiation between the two conditions. They both use fields from the full join. – ypercubeᵀᴹ Jun 21 '11 at 15:11
`buys.buy` is a column from an "inner" table, not an "outer" table, and so there is no outer join (to `buys`) to cancel. – Tony Andrews Jun 21 '11 at 15:28
1

@Tony, it's a `FULL OUTER JOIN`, which means an outer join in both directions. – Dave Costa Jun 21 '11 at 15:40
D'oh I'm sorry - completely missed that!! – Tony Andrews Jun 21 '11 at 15:41

Oracle outer join "entity"

3 Answers3