inner/outer joins

[markcran]

Hi,

I have some confusion about inner and outer joins.

I initalizally thought inner was based on equality and
outer was based on inequality condition, but this produces
cartesean product. There are also left joins and right joins
which seem to have to have different syntaxs such as:
Left Join, Right Join and the syntax (+)

can the sign "+" be used to specify left or right join ?

thanks,

Mark C

 

[Golom]

Equality and inequality differentiate between "equijoins" and "Theta Joins" ... not INNER and OUTER. To illustrate

From A INNER JOIN B ON A.fld1 [red]=[/red] B.fld1

is an "equijoin" where fields are equal

in contrast

From A INNER JOIN B ON A.fld1 [red]<[/red] B.fld1

is a "Theta Join" where fields are compared with some inequality operator (< in this example, although <=, >, >=, <>, etc. could also be used.)

An OUTER join takes ALL records from one table and joins them to another table where the "ON" condition is satisfied or with NULLs if no match is found. For example

From A LEFT OUTER JOIN B ON A.fld1 = B.fld1

retrieves ALL records from A matched to records from B where A.fld1 = B.fld1 AND the remaining unmatched records from A with NULLs in place of the fields from B. A RIGHT OUTER JOIN does the same thing except that it is the table on the RIGHT that ALL records are drawn from.

You can also have OUTER Theta-joins as in

From A LEFT OUTER JOIN B ON A.fld1 >= B.fld1

Finally a cross-join or cartesian product results from

From A, B

results in every record from A being combined with every record from B (i.e. (# of A Records) * (# of B records) in the result.)

 

[Golom]

Oh ... and the second part

can the sign "+" be used to specify left or right join ?


Used in what context?

There was, in earlier definitiions of the SQL standard (and in some implementations), the syntax

Where A.Fld1 [red]*[/red]= B.Fld1

or

Where A.Fld1 =[red]*[/red] B.Fld1

or

Where A.Fld1 [red]*[/red]=[red]*[/red] B.Fld1

to designate a LEFT, RIGHT or FULL OUTER JOINs in the WHERE clause. That has been dropped from the standard and the standard (since SQL'92) has been that JOINS are specified in the FROM clause.

 

[LKBrwnDBA]

Note: The '+' is Oracle "join" syntax.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb