Looking for an explanation of some SELECT-SQL aspects 1

[Stella740pl]

Can someone please help me explain this phenomenon?
While it works, I don’t really understand why it works this way, and it bothers me.

We are using VFP6.
Here is the setup.

The Avg_tmp table holds calculated on-the-spot (from a master table) monthly statistics.

The Template table contains only the names (and more of them than in the Avg_tmp table),
including some placeholder entries, currently inactive entries, etc.,
to match a special format for one of my internal clients’ Excel report.
(The resulting cursor I would just copy into Excel and paste the columns into their places in
that Excel file that holds several years worth of statistics.)

The statements are like this:

SELECT aa.name, ;
bb.avg_sch, ;
bb.avg_sat, ;
bb.avg_sun, ;
bb.avg_wkd ;
FROM template aa ;
LEFT JOIN avg_tmp bb ;
[red] ON aa.name==bb.name AND bb.ccyy=2008 AND bb.mth=7 ;[/red]
INTO CURSOR gl0708_1

SELECT aa.name, ;
bb.avg_sch, ;
bb.avg_sat, ;
bb.avg_sun, ;
bb.avg_wkd ;
FROM template aa ;
LEFT JOIN avg_tmp bb ;
[red] ON aa.name==bb.name ;
WHERE bb.ccyy=2008 AND bb.mth=7 ; [/red]
INTO CURSOR gl0708_2

The first statement works as I would expect it to work, selects all of the records from the Template table,
including all of the placeholders that I need there and whatever matches it from the Avg_tmp table.

The second one, while looks more correct to me than the first one (after all, bb.ccyy=2008 AND bb.mth=7
are filter conditions, not join conditions), don’t do what I expect it to do,
and, in fact, creates an inner join, selecting only those records that are found in both tables
(which would be only the records from the Avg_tmp table).

Am I missing something here?

Thanks,
Stella

 

[Olaf Doschke]

No, this is normal, a where clause will make an inner join out of a left/right join, because this condition MUST be fullfilled to be in the result, ccyy and mth aren't allowed to be NULL.

The ON condition is checking if a record of bb (Avg_tmp) does match, if not NULL values are joined.

compatible would be

SELECT aa.name, ;
bb.avg_sch, ;
bb.avg_sat, ;
bb.avg_sun, ;
bb.avg_wkd ;
FROM template aa ;
LEFT JOIN avg_tmp bb ;
ON aa.name==bb.name ;
WHERE (bb.ccyy=2008 or bb.ccyy is null);
AND (bb.mth=7 or bb.mth is null);
INTO CURSOR gl0708_2


At least in principle, I'm not sure if VFP6 allows the ansi sql syntax IS NULL, otherwise you could use ISNULL().... or go back to the simpler ON condition.

Bye, Olaf.

 

[Stella740pl]

Thanks, Olaf.

No, this is normal, a where clause will make an inner join out of a left/right join, because this condition MUST be fullfilled to be in the result, ccyy and mth aren't allowed to be NULL.

Hm. Kind of makes sense - that the condition MUST be fullfilled to be in the result. Isn't it true about any condition specified anywhere in the SELECT?

On the other hand, ccyy and mth in Avg_tmp table are never null, empty, blank, or anything else, not even zero. In that table, they always have a positive numeric value - they just either equal to 2008 and 7, respectively, or they don't. Why would NULL values be joined? Seems so straight forward, but apparently, it is not.

... or go back to the simpler ON condition

Yes, that's what I did - went back to the simpler ON clause containig all of the conditions - I just have a hard time calling it "simpler", that's why I asked.
I must have gotten "out of SQL shape".

 

[Olaf Doschke]

I'll put it in slightly different words:

The ON condition just checks, if there is a matching record in the joined table, if not, that does not mean the record is totally skipped in the result, but instead null values are joined for the fields of the joined table.

This doesn't mean these Null values come from that joined table, this just means there is no matching record there.

A where condition on the other side demands that that field is a specific value, not Null, therefore you loose any record in the result not having a match in the joined table and that makes it an inner join.

So the basic rule is, if you don't want an inner join, keep out any conditions on the joined table from the where clause, put them in the ON join condition. You're just defining what records to join and that's all you want then, as you want all records from the first table.

Bye, Olaf.

 

[Stella740pl]

A where condition on the other side demands that that field is a specific value, not Null, therefore you loose any record in the result not having a match in the joined table and that makes it an inner join.

Thanks. Got it.

So the basic rule is, if you don't want an inner join, keep out any conditions on the joined table from the where clause, put them in the ON join condition. You're just defining what records to join and that's all you want then, as you want all records from the first table.

I did arrive to the basic rule, empirically, just needed an explanation of why it is so, and you provided it. Thanks.

I seem to remember that I already tripped on a similar issue before, but no more than once or twice in years. It looks like most of the time I need inner joins, and outer joins are rare and far between in my programs - and even those usually have just join conditions and no additional criteria in WHERE clause.

Thanks again.