Re: [SQL] Beginner question - select with multiple tables

On Thu, 21 Jan 1999, Dave Inskeep wrote:

> I expect the query to return the node field from the four records in
> vall_bod1 that contain 'brake'. If I query over vall and vall_bod1
> alone, I get the four records. If I query over vall, vall_bod1, and
> vall_bod2, I get different results depending if vall_bod2 contains ANY
> records, matching or not. If vall_bod2 has no records, the query
> returns no records, period. If vall_bod2 contains even a single record
> that does not match the query, the four expected records in vall_bod1
> are returned.
>
> My point is that the query produces different results if any of the
> tables in the query contain no records. Since I'm using an OR between
> the tables in my where clause, I find this strange.
>

OK. This is the expected behavior, but let me explain why.

When you do a join, you are actually doing a cartezian product of the
three tables. That is, in theory, each record from vall is matched against
each record from vall_bod1, and with each row from vall_bod2.

This means that initially, before the "where" part rejects unwanted
combinations of rows, you get a very large set of combined rows. Suppose
vall has N records, vall_bod1 has M1 records and vall_bod2 has M2 records,
the initial set of rows from which your required rows is selected contains
N*M1*M2 combined rows.

Follow so far? These are the candidate rows, and they are filtered out
according to your where clause.

But here is the rub: if one of the tables is empty, say vall_bod2, then M2
is zero, right? So, your where clause is applied to a set containing
N*M1*0 records, that is, to an empty set.

Ah... Nothing from nothing gives nothing.

So, as was suggested here, you need to use a union rather than a join.

Herouth