Re: Classic trap : using SQL while not fully competent

[Adam Glauser <adamglauser@xxxxxxxxxxxx>]

Joe Pluta wrote:
> David FOXWELL wrote:
> > I was going to cry for help :
> >
> > I know I have a record on the left that ain't on the right  so I did
> >
> > SELECT * FROM prdcap exception join adhtst on ad1cad=tadhno
> > WHERE tcrtcd ='14'
> >
> > Which returned no record.
> >   
> Since tcrtcd is in the right-hand table, this technically doesn't even 
> make sense.  By definition, you're saying you want to create a result 
> set on rows that have no matching data in the right-hand table.

It seems to me that this confusion comes up time and again.  It caught 
me this time when I wasn't thinking really carefully.  "Exception join" 
really means "left exception join", when I think many people expect a 
"full exception join".  As has been discussed previously, there is no 
support for "full exception join" currently.  It needs to be built by 
doing something like
SELECT <fields> FROM <file1> LEFT EXCEPTION JOIN <file2> ON <join-criteria>
UNION [ALL]
SELECT <fields> FROM <file1> RIGHT EXCEPTION JOIN <file2> ON <join-criteria>

This is the reason that we sometimes see the advice "always fully 
specify the join type."  That is, don't rely on the fact that "EXCEPTION 
JOIN" really means "LEFT EXCEPTION JOIN", similarly for outer joins.