Re: Why is this a cross join?

On 02/17/2013 02:09 PM, Tim Uckun wrote:
>>
>> In some way, every join is a cross join, with the results filtered according to the specificity of the join conditions. In this case:
>>
>> inner join model_configurations mc on left(crm.customer_class, 6) = left(mc.sap_code,6)
>>
>> "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.
>>
>
> I guess I am not explaining it properly..
>
> 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?
>
> inner join model_configurations mc on mc.first_6 = crm.first_6
>
>
http://www.postgresql.org/docs/9.2/interactive/sql-select.html

...CROSS JOIN and INNER JOIN produce a simple Cartesian product, the
same result as you get from listing the two items at the top level of
FROM, but restricted by the join condition (if any). CROSS JOIN is
equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by
qualification. These join types are just a notational convenience, since
they do nothing you couldn't do with plain FROM and WHERE...

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com