Re: Why is this a cross join?

On 18/02/2013, at 9:09 AM, Tim Uckun <timuckun@gmail.com> wrote:

>>=20
>> In some way, every join is a cross join, with the results filtered =
according to the specificity of the join conditions. In this case:
>>=20
>> inner join model_configurations mc on left(crm.customer_class, 6) =3D =
left(mc.sap_code,6)
>>=20
>> "customer_class" sounds like a fairly generic sort of field, so you'd =
expect many matches. Truncating the fields is likely to make this even =
less specific, returning more results.
>>=20
>=20
> I guess I am not explaining it properly..
>=20
> Say I created new columns on both tables called "first_6" and
> populated them with the substrings.  If I did a inner join or a left
> join on those fields would I still get a cross join?

No, it would be no different if you created new columns - the join =
condition is the same.

You're not actually getting a cross join, that would be many more =
records than 9million - you're just not setting a specific enough =
filter.

Cheers,

Tony