Oracle: how to make a big outer Join

Question

I Want that all the record from the ROPT table to be displayed even if the the JOIN condition with the ELF table is violated.

  SELECT   1      
   FROM   conf_raggr_opztar ropt,
           tar_opzioni_tariffarie opt,
           conf_raggruppamenti_forn rgf,
           conf_forniture_rel_ragg forg,
           conf_forniture forn,
           conf_elementi_fatturabili elf,
           tar_voci_fatturabili vof,
           base_fasce_orarie fas
   WHERE       ropt.opt_opzione_tariffaria_id = opt.opt_opzione_tariffaria_id
           AND rgf.rgf_raggruppamento_forn_id = ropt.rgf_raggruppamento_forn_id
           AND forg.rgf_raggruppamento_forn_id = rgf.rgf_raggruppamento_forn_id
           AND forg.forn_fornitura_id = forn.forn_fornitura_id
           AND forn.forn_fornitura_id = 'QJlXmOFZPF3eAlAG'
           AND elf.ROPT_RAGGR_OPZTAR_ID(+) = ropt.ropt_raggr_opztar_id
           AND elf.COID_CONTRATTUARIO_ID(+) = ropt.COID_CONTRATTUARIO_ID
           AND elf.ROPT_DATA_INI(+) = ropt.ROPT_DATA_INI
           AND elf.edw_partition = forn.EDW_PARTITION
           AND elf.elf_flag_ann(+) = 'N'
           And  elf.ELF_DATA_VER_FIN = to_date('31/12/9999','DD/MM/YYYY')
           AND elf.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID
           AND fas.FAS_FASCIA_ORARIA_ID = elf.FAS_FASCIA_ORARIA_ID
ORDER BY ELF_VERSIONE desc;

* ANSI JOIN VERSION (it acts like the first one)

  SELECT   1
FROM CONF_RAGGR_OPZTAR ropt
   INNER JOIN TAR_OPZIONI_TARIFFARIE OPT
     ON (ropt.OPT_OPZIONE_TARIFFARIA_ID = opt.OPT_OPZIONE_TARIFFARIA_ID)
   INNER JOIN CONF_RAGGRUPPAMENTI_FORN rgf
     ON (rgf.RGF_RAGGRUPPAMENTO_FORN_ID = ropt.RGF_RAGGRUPPAMENTO_FORN_ID)
   INNER JOIN CONF_FORNITURE_REL_RAGG forg
     ON (forg.RGF_RAGGRUPPAMENTO_FORN_ID = rgf.RGF_RAGGRUPPAMENTO_FORN_ID)
   INNER JOIN CONF_FORNITURE forn
     ON (forg.FORN_FORNITURA_ID = forn.FORN_FORNITURA_ID)
   LEFT OUTER JOIN CONF_ELEMENTI_FATTURABILI elf
     ON (elf.ROPT_RAGGR_OPZTAR_ID = ropt.ROPT_RAGGR_OPZTAR_ID AND
         elf.COID_CONTRATTUARIO_ID = ropt.COID_CONTRATTUARIO_ID AND
         elf.ROPT_DATA_INI = ropt.ROPT_DATA_INI AND
         elf.EDW_PARTITION = forn.EDW_PARTITION)
--   LEFT OUTER JOIN TAR_VOCI_FATTURABILI vof
--     ON (elf.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID)
--   LEFT OUTER JOIN BASE_FASCE_ORARIE fas
--     ON (fas.FAS_FASCIA_ORARIA_ID = elf.FAS_FASCIA_ORARIA_ID)
   WHERE forn.FORN_FORNITURA_ID = 'QJlXmOFZPF3eAlAG' AND
         elf.ELF_FLAG_ANN = 'N' AND
         elf.ELF_DATA_VER_FIN = TO_DATE('31/12/9999','DD/MM/YYYY')
ORDER BY ELF_VERSIONE DESC; 

Answer 1

I recommend that you learn and use the ANSI-style join operators such as INNER JOIN, LEFT OUTER JOIN, etc. They are much clearer and easier to understand than putting everything into the WHERE clause. In the case of your statement I believe it can be rewritten as follows:

SELECT 1
   FROM CONF_RAGGR_OPZTAR ropt
   INNER JOIN TAR_OPZIONI_TARIFFARIE OPT
     ON (ropt.OPT_OPZIONE_TARIFFARIA_ID = opt.OPT_OPZIONE_TARIFFARIA_ID)
   INNER JOIN CONF_RAGGRUPPAMENTI_FORN rgf
     ON (rgf.RGF_RAGGRUPPAMENTO_FORN_ID = ropt.RGF_RAGGRUPPAMENTO_FORN_ID)
   INNER JOIN CONF_FORNITURE_REL_RAGG forg
     ON (forg.RGF_RAGGRUPPAMENTO_FORN_ID = rgf.RGF_RAGGRUPPAMENTO_FORN_ID)
   INNER JOIN CONF_FORNITURE forn
     ON (forg.FORN_FORNITURA_ID = forn.FORN_FORNITURA_ID)
   LEFT OUTER JOIN CONF_ELEMENTI_FATTURABILI elf
     ON (elf.ROPT_RAGGR_OPZTAR_ID = ropt.ROPT_RAGGR_OPZTAR_ID AND
         elf.COID_CONTRATTUARIO_ID = ropt.COID_CONTRATTUARIO_ID AND
         elf.ROPT_DATA_INI = ropt.ROPT_DATA_INI AND
         elf.EDW_PARTITION = forn.EDW_PARTITION AND
         elf.ELF_FLAG_ANN = 'N' AND
         elf.ELF_DATA_VER_FIN = TO_DATE('31/12/9999','DD/MM/YYYY'))
   LEFT OUTER TAR_VOCI_FATTURABILI vof
     ON (elf.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID)
   LEFT OUTER BASE_FASCE_ORARIE fas
     ON (fas.FAS_FASCIA_ORARIA_ID = elf.FAS_FASCIA_ORARIA_ID)
   WHERE forn.FORN_FORNITURA_ID = 'QJlXmOFZPF3eAlAG'
   ORDER BY ELF_VERSIONE DESC; 

Note that the three comparisons in the WHERE clause above could be put into the joins - I put them in the WHERE clause to demonstrate that you can do so. I suspect the optimizer would use these comparisons however it wanted.

Share and enjoy.

---- EDIT

Also note that putting two of the conditions for table 'elf' in the WHERE clause forced 'elf' to be treated as if it had been inner-joined. Note to self: try to be a bit less didactic in the future... :-)

Answer 2

Probably the use of the USING construct of ANSI JOIN and the use of subqueries will make your query easier to write.

Look at this sample it should let your query work.

SELECT   1
FROM CONF_RAGGR_OPZTAR ropt
    JOIN TAR_OPZIONI_TARIFFARIE OPT using (OPT_OPZIONE_TARIFFARIA_ID)
    JOIN CONF_RAGGRUPPAMENTI_FORN rgf using (RGF_RAGGRUPPAMENTO_FORN_ID)
    JOIN CONF_FORNITURE_REL_RAGG forg using (RGF_RAGGRUPPAMENTO_FORN_ID)
    JOIN CONF_FORNITURE forn using (FORN_FORNITURA_ID)
    LEFT JOIN (
            select *
            from CONF_ELEMENTI_FATTURABILI 
            where ELF_FLAG_ANN = 'N' 
                AND ELF_DATA_VER_FIN = TO_DATE('31/12/9999','DD/MM/YYYY')
        ) elf using (ROPT_RAGGR_OPZTAR_ID,COID_CONTRATTUARIO_ID,ROPT_DATA_INI,EDW_PARTITION)
--   LEFT OUTER JOIN TAR_VOCI_FATTURABILI vof
--     ON (elf.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID)
--   LEFT OUTER JOIN BASE_FASCE_ORARIE fas
--     ON (fas.FAS_FASCIA_ORARIA_ID = elf.FAS_FASCIA_ORARIA_ID)
WHERE FORN_FORNITURA_ID = 'QJlXmOFZPF3eAlAG'
ORDER BY  elf.ELF_VERSIONE DESC; 

Anyway if you're not selecting any column from CONF_ELEMENTI_FATTURABILI why do you want to outer join on that table?

Isn't it senseless?!!! You would get the same result except for duplicates with the following query:

SELECT   1
FROM CONF_RAGGR_OPZTAR ropt
    JOIN TAR_OPZIONI_TARIFFARIE OPT using (OPT_OPZIONE_TARIFFARIA_ID)
    JOIN CONF_RAGGRUPPAMENTI_FORN rgf using (RGF_RAGGRUPPAMENTO_FORN_ID)
    JOIN CONF_FORNITURE_REL_RAGG forg using (RGF_RAGGRUPPAMENTO_FORN_ID)
    JOIN CONF_FORNITURE forn using (FORN_FORNITURA_ID)
--   LEFT OUTER JOIN TAR_VOCI_FATTURABILI vof
--     ON (elf.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID)
--   LEFT OUTER JOIN BASE_FASCE_ORARIE fas
--     ON (fas.FAS_FASCIA_ORARIA_ID = elf.FAS_FASCIA_ORARIA_ID)
WHERE FORN_FORNITURA_ID = 'QJlXmOFZPF3eAlAG'
ORDER BY  null DESC; 

Answer 3

You need to understand your query

By placing (+) in direct joins for elf, you have successfully mentioned that "I don't care if elf has data, I want to show ropt row"

  AND elf.ROPT_RAGGR_OPZTAR_ID(+) = ropt.ropt_raggr_opztar_id
  AND elf.COID_CONTRATTUARIO_ID(+) = ropt.COID_CONTRATTUARIO_ID
  AND elf.ROPT_DATA_INI(+) = ropt.ROPT_DATA_INI

But what you don't realize is that you have an indirect join as well

ropt joined to rgf

AND rgf.rgf_raggruppamento_forn_id = ropt.rgf_raggruppamento_forn_id

rgf joined to forg, forg to forn and finally forn to elf

AND forg.rgf_raggruppamento_forn_id = rgf.rgf_raggruppamento_forn_id
AND forg.forn_fornitura_id = forn.forn_fornitura_id
AND elf.edw_partition = forn.EDW_PARTITION

So the cycle of joins indirectly joins elf to ropt. One way will be to have outer join for elf to forn as well, but at the end it will make sense to understand your requirement and then join tables.

--Edit--

As pointed out that we cannot have 2 outer joins, one (ugly) workaround can be something like

SELECT   1      
   FROM   
           tar_opzioni_tariffarie opt,
           conf_raggruppamenti_forn rgf,
           conf_forniture_rel_ragg forg,
           conf_forniture forn,
           (
           select * from //or columns needed 
       conf_raggr_opztar ropt,
           conf_elementi_fatturabili elf where 
       elf.ROPT_RAGGR_OPZTAR_ID(+) = ropt.ropt_raggr_opztar_id
           AND elf.COID_CONTRATTUARIO_ID(+) = ropt.COID_CONTRATTUARIO_ID
           AND elf.ROPT_DATA_INI(+) = ropt.ROPT_DATA_INI
           ) elf_ropt,
           tar_voci_fatturabili vof,
           base_fasce_orarie fas
   WHERE       elf_ropt.opt_opzione_tariffaria_id = opt.opt_opzione_tariffaria_id
           AND rgf.rgf_raggruppamento_forn_id = elf_ropt.rgf_raggruppamento_forn_id
           AND forg.rgf_raggruppamento_forn_id = rgf.rgf_raggruppamento_forn_id
           AND forg.forn_fornitura_id = forn.forn_fornitura_id
           AND forn.forn_fornitura_id = 'QJlXmOFZPF3eAlAG'
           AND elf_ropt.edw_partition(+) = forn.EDW_PARTITION
           AND elf_ropt.elf_flag_ann(+) = 'N' //is this needed actually?
           And  elf_ropt.ELF_DATA_VER_FIN = to_date('31/12/9999','DD/MM/YYYY')
           AND elf_ropt.VOF_VOCE_FATTURABILE_ID = vof.VOF_VOCE_FATTURABILE_ID
           AND fas.FAS_FASCIA_ORARIA_ID = elf_ropt.FAS_FASCIA_ORARIA_ID
ORDER BY ELF_VERSIONE desc;

Answer 4

If I understood your question correctly, you want to use a LEFT JOIN.