What is "left outer join"..?
Hi All, I have a question about "left outer join"... What is "left outer join"..? and how to use it...? Thanks,
Let me give you a short example:
Person( person_key integer, name char(30));
Primary key on code
Values: (1, "Me"), (2, "You"), (3, "Them")
Children( person_key integer, child_key integer, childname char(30));
Primary key on (person_key + child_key)
Values: (1, 1, "My first child"), (1, 2, "My last child"), (3, 1, "Their
only child")
If you try to list all persons along with their children, you can try:
SELECT Person.name, Children.child_key, Children.childname
FROM Person, Children
WHERE Children.person_key = Person.person_key
You will get:
Me 1 My first child
Me 2 My last child
Them 1 Their only child
If you use a left outer join between the two tables, the result will be:
SELECT Person.name, Children.child_key, Children.childname
FROM Person, OUTER Children
WHERE Children.person_key = Person.person_key
Me 1 My first child
Me 2 My last child
You <null> <null>
Them 1 Their only child
The left outer join select every rows from Person and establish a join
when possible with Children. When no join can be found, it return one
row from the first table along with null values for the fields selected
from the outer table.
You can OUTER many tables, at different levels using parenthesis and the
syntax can become quite complicated as 'FROM t1, OUTER t2, OUTER t3,t4'
isn't equal to 'FROM t1, OUTER t2, OUTER t3, OUTER t4'.
Well, some strange results may appear and I personnaly try to avoid
OUTERs.
Bongsoo Chong wrote:
> Hi All,
>
> I have a question about "left outer join"...
>
> What is "left outer join"..?
>
> and how to use it...?
>
> Thanks,
Bongsoo Chong wrote: > > Hi All, > > I have a question about "left outer join"... > > What is "left outer join"..? > > and how to use it...? Left and right outer join refers to whether the first named of a pair of tables is driving the join, ie its rows are "master" rows of the result set, or the second named table respectively. Informix only implements left outer joins meaning that the desired row MUST exist in the left, or first named, table in the join to be returned. Outer join refers to a join where data from the master table is returned EVEN if there is no matching row in the subordinate table. In such case the missing row from the subordinate table is replaced with NULL values. So if you query an orders table and join it to the line_items table in a normal, or inner, join if an order has no line items it would not be part of the result set. However, if you want to report ALL orders, even those with no line items you will need to perform an outer join with the orders table as master and the line_items table as subordinate. Art S. Kagel