LEFT JOIN alternative

[Hugo Larson]

Hi,

Consider this SQL:

select * from PRODUCT p
left join PRODUCTCATEGORY pc on p.CATEGORY_ID = pc.ID

I have LEFT join to include any PRODUCT that does not have a belonging (orphan) PRODUCTCATEGORY.

After a while we notices that LEFT JOIN is extremely slow.
Is there a clever way fetch orphan PRODUCT without LEFT JOIN?

The actual SQL is more complicated.

Thanks,
Hugo

[Ariel Álvarez]

Try:

Select * from product p where not exists (select 1 from productcategory pc where pc.id=p.category_id)

Regards,

Ariel

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/1240736290.404201.1636883101039%40mail.yahoo.com.

[Svein Erling Tysvær]

I only know older versions of Firebird (still on 2.5), but there I always make sure to put inner joins before left joins if possible, i.e. I normally write select * from PRODUCTS p join SALES s left join PRODUCTCATEGORY pc and avoid writing select * from PRODUCTS p left join PRODUCTCATEGORY pc join SALES s, since the optimizer doesn't work equally well with joins after the first outer join (there are a few exceptions).

Though to only answer your original question, yes, it is possible to have subselects as long as they produce single results, select *, (select pc.NAME from PRODUCTCATEGORY pc where p.ID =pc.CATEGORY_ID) as CATEGORY_NAME from PRODUCTS p

Hth,

Set

--

[Hugo Larson]

I'm on FB 2.5 too :)
You mean that LEFT JOIN should be last?

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CAKC2LgwaunLYuyjU8Xu9%3DVhw%2BVn-PbunNkRZyTa2dkTyBF%2BNYA%40mail.gmail.com.