Outer Join Explain Plz

[imranishaque]

Hi All,

I was going through the "1146115A_SQL Reference to Data Manipulation Statements" and I got the idea about the outer join but as not able to get the meaning out of the last line ((((((The 1 enclosed in the brackets )))))). Can somebody explain Plz?

The outer join is meant to provide a method for regaining the "lost" information the inner join does not report. The outer join is an extended inner join that projects not only those rows that have commonality between the joined tables, but also those rows that have no counterpart in the other relation.

Depending on how you write an outer join, it can project the inner join rows plus any of the following: the nonmatching rows of the left table, the nonmatching rows of the right table, or the nonmatching rows from both.((((((( The attributes of the complementary row sets of an outer join are represented in the result set by nulls.))))))

Thanks.

[joedsilva]

Ok let's assume you are doing an full outer join on employee and dept tables based on deptno.

So for deptnos in employee table which does not have a record in dept table the column values (this is the complimentary row) would appear as null

And for dept table records with deptno not appearing in employee table, the corresponding values (the complimentary row) for employee columns would appear as null.

So basically complimentary row(s) for a particular record from a table is the set of records that's retrieved from the other table. (ie records with same values for joining columns). In the special case of outer join this would be nulls for join values that doesn't have a corresponding record in the other table.

[imranishaque]

Thanks A Zillion.