Postgres – Fix INNER JOIN Returning Zero Rows with COALESCE

join;postgresql

I have the following query: 

SELECT CPC.importe
FROM fac_factura FAC 
INNER JOIN fac_concepto_periodo_control CPC ON CPC.id_factura = FAC.id
INNER JOIN fac_factura_deuda_pendiente FDP ON FDP.id_factura = FAC.id
INNER JOIN fac_item_facturacion FIF ON CPC.id_item_facturacion = FIF.id
WHERE FIF.codigo = 427
AND FDP.estado ILIKE 'CANCELADA'
AND CPC.estado ILIKE 'FACTURADO'

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.

How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.

Best Answer

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.

How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.

If the join conditions are not being fulfilled you have 0-rows. An INNER JOIN on false returns 0-rows: not one row with a null value, not one row with a zero.

SELECT x,y
FROM ( VALUES (1) ) AS t(x)
JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y 
---+---
(0 rows)

Returns nothing. What you want is an outer join.

SELECT x,y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y 
---+---
 1 |  
(1 row)

Now that you have a row, you can use COALESCE

SELECT x, coalesce(y,0) AS y
FROM ( VALUES (1) ) AS t(x)
LEFT OUTER JOIN ( VALUES (2) ) AS g(y)
  ON false;
 x | y 
---+---
 1 | 0
(1 row)
Related Question