left Join

[system]

Hi all
I want to put left join in my query, but there is only option for outer join. could any body guide me how to impliment left join( syntax).
regrads

---

GBS74 (BOB member since 2007-01-23)

[system]

An outer join is a left or right join, depending on which table is in the outer join tab on the left (the table on hte left side of the tab is the one where you get all rows).

---

Werner Daehn (BOB member since 2004-12-17)

[system]

The only caveat is that all where clause criteria become join criteria if you use DI’s left outer join. If that is not sufficient (ie, if you need some filtering BEFORE the join), you can get the same effect with a pushdown_sql. See my example here:

120×120 10.4 KB

Okay, I have found a way to solve my problem but I need to know if there are any pitfalls with my solution. I have two tables that need to be left outer joined on 3 columns. In addition, I need to limit 4 columns on one of the tables but NOT include those in the join (in other words, prefilter the join). When I put all 7 criteria into the where clause and tell DI it is an outer join, it puts all the criteria into the join. IE: select * from tbl_21, tbl_23 WHERE (TBL_23.I_CLIE = TBL_21.I_CLIE (…

• Ernie

---

eepjr24 (BOB member since 2005-09-16)

[system]

Many Thanks

---

GBS74 (BOB member since 2007-01-23)

[system]

@eepjr24

The only caveat is that all where clause criteria become join criteria if you use DI’s left outer join. If that is not sufficient (ie, if you need some filtering BEFORE the join), you can get the same effect with a pushdown_sql. See my example here:

Left outer join in a pushdown_sql

• Ernie

Wow, what timing. This issue just completely bit us this week! Under 11.7.0, if you had outer joins with filtering criteria, it didn’t push down the join at all. But under 11.7.3, it generates the wrong SQL and pushes that down.

See this for a discussion of the outer-join-with-filtering issue from an ANSI SQL perspective:

http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/

---

dnewton (BOB member since 2004-01-30)

[system]

I just found one exception to the above yesterday, by the way. Have not yet found a way around it, either. If the left join in question is a SELF join, then as far as I can tell there is no way to prevent DI from putting all the criteria into the ON clause (or in classic Oracle syntax, every criteria has a (+)). This ended up in me adding another query and doing the filtration inside DI. The cost was not extremely high (performance wise), but that will not always be the case.

• Ernie

---

eepjr24 (BOB member since 2005-09-16)

[system]

I appear to have found another ‘issue’ with this.

If any of your tables are only used in the pushdown_sql where clause and not the select clause then they do not appear in the from clause.

I have a query with 6 tables of which 3 are just used to define joins. Using pushdown_sql allows me to control the outer joins to my satisfaction, but this is moot when tables are not included in the from clause.

---

dfoster (BOB member since 2008-01-08)

[system]

Hrm. Is there a reason they cannot be in the select clause? Would it cause cartesian effects to have it there for example? If so, I get your point, but if not you can just include a single column and then discard it after the join is done?

• Ernie

---

eepjr24 (BOB member since 2005-09-16)

[system]

Hi Ernie,

I have had to do just as you suggested and put a dummy column into the result set that contains the join keys.

A little bit frustrating, but fairly easy to work around.

---

dfoster (BOB member since 2008-01-08)