Doubt regarding the IN in Oracle

Question

I’m putting together a query and bumped into a question.

My query is like this:

SELECT BASI_030.NIVEL_ESTRUTURA NIVEL,
       BASI_030.REFERENCIA GRUPO,
       BASI_030.DESCR_REFERENCIA,
       BASI_030.CONTA_ESTOQUE,
       BASI_020.TIPO_PRODUTO
  FROM BASI_030
  JOIN BASI_020
    ON BASI_030.NIVEL_ESTRUTURA = BASI_020.BASI030_NIVEL030
   AND BASI_030.REFERENCIA = BASI_020.BASI030_REFERENC
 WHERE BASI_030.NIVEL_ESTRUTURA = 2
   AND BASI_030.CONTA_ESTOQUE IN (77, 87, 92)
   AND BASI_030.REFERENCIA IN ( SELECT MCREF.REFERENCIA
                                  FROM MARK_CAMPANHA_REF MCREF
                                 WHERE MCREF.ID_CAMPANHA = 16 )

It is working, but when the MARK_CAMPANHA_REF table has no REFERENCE, I would have to bring all references contained in the BASI_030 table

As it is, when the MARK_CAMPANHA_REF table has no records for ID_CAMPANHA, the query returns no value.

Could you help me?

Answer 1

It would be a mix of LEFT JOIN with CTE to realize the logic.

WITH sql AS (
    SELECT BASI_030.NIVEL_ESTRUTURA NIVEL,
           BASI_030.REFERENCIA GRUPO,
           BASI_030.DESCR_REFERENCIA,
           BASI_030.CONTA_ESTOQUE,
           BASI_020.TIPO_PRODUTO,
           MCREF.REFERENCIA REFERENCIA_MCREF   
    FROM BASI_030   
    JOIN BASI_020
       ON BASI_030.NIVEL_ESTRUTURA = BASI_020.BASI030_NIVEL030    
      AND BASI_030.REFERENCIA = BASI_020.BASI030_REFERENC  
    LEF JOIN MARK_CAMPANHA_REF MCREF
        ON MCREF.ID_CAMPANHA = 16
       AND MCREF.REFERENCIA = BASI_030.REFERENCIA
    WHERE BASI_030.NIVEL_ESTRUTURA = 2    
      AND BASI_030.CONTA_ESTOQUE IN (77, 87, 92) 
)
  , test AS (
    SELECT count(*) qtd
    FROM sql
    WHERE REFERENCIA_MCREF IS NOT NULL
)
SELECT *
FROM sql
WHERE  (
        CASE
        WHEN ((SELECT qtd
               FROM test) > 0)
          THEN
            REFERENCIA_MCREF IS NOT NULL
        ELSE
          1 = 1
        END)