Re: Why is this a cross join?

[Alban Hertroys <haramrae@xxxxxxxxx>]

On Feb 17, 2013, at 9:16, Tim Uckun <timuckun@xxxxxxxxx> wrote:

I have a seemingly simple query that returns way too many records.
Here is the count query

select count(*) from (
   select crm.*
   from sap_crm_sales crm
    inner join model_configurations mc on left(crm.customer_class, 6)
= left(mc.sap_code,6)
) as s

The crm table has about 44K records, the model table has about 1K the
join produces about 9 million  records

Apparently the first 6 characters of those fields are quite common, which gives you a result for every possible combination of the same 6-character value.

Alban Hertroys

--

If you can't see the forest for the trees,

cut the trees and you'll find there is no forest.