JeffP JeffP - 20 days ago 6
SQL Question

SQL Alternative to left outer join

Is this a good alternative to LEFT OUTER JOIN, performance-wise? Is it the best?

SELECT a.PRODUCT_ID
,a.PROD_NAME
,a.PRODUCTTYPE_ID
,a.SUPPLIER_ID
,t.PRODTYPE_NAME
,s."NAME" SUPPLIER_NAME
FROM PRODUCT a
INNER JOIN (
SELECT NULL PRODUCTTYPE_ID
,NULL PRODTYPE_NAME
FROM rdb$database

UNION ALL

SELECT PRODUCTTYPE_ID
,PRODTYPE_NAME
FROM PRODUCTTYPE
) t
ON (t.PRODUCTTYPE_ID = a.PRODUCTTYPE_ID) OR (t.PRODUCTTYPE_ID IS NULL AND a.PRODUCTTYPE_ID IS NULL)
INNER JOIN (
SELECT NULL SUPPLIER_ID
,NULL "NAME"
FROM rdb$database

UNION ALL

SELECT SUPPLIER_ID
,"NAME"
FROM SUPPLIER
) s
ON (s.SUPPLIER_ID = a.SUPPLIER_ID) OR (s.SUPPLIER_ID IS NULL AND a.SUPPLIER_ID IS NULL)


Index on PRODUCT_ID, PRODUCTTYPE_ID, SUPPLIER_ID in Table PRODUCT and their respective tables by virtue of Primary Key and Foreign Keys

Answer

No!

You should use left join. Here are some reasons why your method is (in almost all likelihood) going to be worse:

  • You are doing multiple joins instead of a single join. A single join should be faster.
  • You have or in the on clause. Oy! That's a performance killer.
  • The use of union all before the join makes using indexes much more difficult. And statistics are likely to be off (although I don't know if Firebird uses statistics to optimize queries).
  • Your complicated query is less likely to use indexes.

A simple left join should use indexes and have much better performance.