title: How to convert NULL to 0 in LEFT OUTER JOIN SQL Select statement
body: <p>I have a SQL query that implements a LEFT OUTER JOIN to compile different tables in a single one. I want to be able to join results in a same table, but according to different parameters (where clause).</p>
  
  <p>So, this is the structure of the tables I'm joining:</p>
  
  <h2>dbo.Medios_Pauta</h2>
  
  <p>ID (PK - int)</p>
  
  <p>Nombre (varchar)</p>
  
  <p>UsrCr (varchar)</p>
  
  <p>FechaCr (datetime)</p>
  
  <h2>dbo.N_Preinscripcion</h2>
  
  <p>ID (PK - int)</p>
  
  <p>Periodo (varchar)</p>
  
  <p>MedioId (int)</p>
  
  <p>...Other ones...</p>
  
  <h2>dbo.N_Insc_Preg_Control</h2>
  
  <p>ID (PK - int)</p>
  
  <p>IdPre (int - foreing key to dbo.N_Preinscripcion.ID)</p>
  
  <h2>dbo.N_Insc_Posg_Control</h2>
  
  <p>ID (PK - int)</p>
  
  <p>IdPre (int - foreing key to dbo.N_Preinscripcion.ID)</p>
  
  <p>Ok, so the query I've built is working fine, but whenever there's a NULL value it will bring "NULL" in the table. I want to transform that NULL to zero (0).</p>
  
  <p>I've tried using ISNULL(COUNT(<em>), 0) AND COALESCE(COUNT(</em>), 0) but it still giving a NULL.</p>
  
  <p>Here's the code of the query with ISNULL:</p>
  
  <pre><code>SELECT
      [Inscritos_Preg].[MedioId],
      [Inscritos_Preg].[regs1],
      [Inscritos_Posg].[regs2],
      SUM([Inscritos_Preg].[regs1] + [Inscritos_Posg].[regs2])  AS [Inscritos]  
      FROM
          (
              SELECT
                  [dbo].[Medios_Pauta].[ID] AS [MedioId],
                  ISNULL(COUNT(*), 0) AS [regs1]
              FROM [dbo].[N_Preinscripcion]
  
              INNER JOIN
                  [dbo].[Medios_Pauta]
                  ON [dbo].[Medios_Pauta].[ID] = [dbo].[N_Preinscripcion].[MedioId] 
  
              INNER JOIN
                  [dbo].[N_Insc_Preg_Control] 
                  ON [dbo].[N_Insc_Preg_Control].[IdPre] = [dbo].[N_Preinscripcion].[ID]
  
              WHERE
                  ([dbo].[N_Preinscripcion].[Periodo] = @prd)
  
              GROUP BY
                  [dbo].[Medios_Pauta].[ID],
                  [dbo].[Medios_Pauta].[Nombre]
          ) [Inscritos_Preg]
          LEFT OUTER JOIN
          (
              SELECT
                  [dbo].[Medios_Pauta].[ID] AS [MedioId],
                  ISNULL(COUNT(*), 0) AS [regs2]
              FROM [dbo].[N_Preinscripcion]
  
              INNER JOIN
                  [dbo].[Medios_Pauta]
                  ON [dbo].[Medios_Pauta].[ID] = [dbo].[N_Preinscripcion].[MedioId] 
  
              INNER JOIN
                  [dbo].[N_Insc_Posg_Control] 
                  ON [dbo].[N_Insc_Posg_Control].[IdPre] = [dbo].[N_Preinscripcion].[ID]
  
              WHERE
                  ([dbo].[N_Preinscripcion].[Periodo] = @prd)
  
              GROUP BY
                  [dbo].[Medios_Pauta].[ID],
                  [dbo].[Medios_Pauta].[Nombre]
          ) [Inscritos_Posg]
          ON [Inscritos_Preg].[MedioId] = [Inscritos_Posg].[MedioId]   
  GROUP BY 
      [Inscritos_Preg].[MedioId],
      [Inscritos_Preg].[regs1],  
      [Inscritos_Posg].[MedioId],
      [Inscritos_Posg].[regs2]
  </code></pre>
  
  <p>And this is this query's result:</p>
  
  <pre><code>MedioId   regs1    regs2    Inscritos
  1         8        9        17
  4         75       16       91
  23        16       NULL     NULL
  24        2        NULL     NULL
  25        164      127      291
  28        8        NULL     NULL
  29        3        1        4
  30        1        2        3
  </code></pre>
  
  <p>Now, when I use COALESCE I'm getting the exact same result, with NULL. Here's the code:</p>
  
  <pre><code>SELECT
      [Inscritos_Preg].[MedioId],
      [Inscritos_Preg].[regs1],
      [Inscritos_Posg].[regs2],
      SUM([Inscritos_Preg].[regs1] + [Inscritos_Posg].[regs2])  AS [Inscritos]  
      FROM
          (
              SELECT
                  [dbo].[Medios_Pauta].[ID] AS [MedioId],
                  COALESCE(COUNT(*), 0) AS [regs1]
              FROM [dbo].[N_Preinscripcion]
  
              INNER JOIN
                  [dbo].[Medios_Pauta]
                  ON [dbo].[Medios_Pauta].[ID] = [dbo].[N_Preinscripcion].[MedioId] 
  
              INNER JOIN
                  [dbo].[N_Insc_Preg_Control] 
                  ON [dbo].[N_Insc_Preg_Control].[IdPre] = [dbo].[N_Preinscripcion].[ID]
  
              WHERE
                  ([dbo].[N_Preinscripcion].[Periodo] = @prd)
  
              GROUP BY
                  [dbo].[Medios_Pauta].[ID],
                  [dbo].[Medios_Pauta].[Nombre]
          ) [Inscritos_Preg]
          LEFT OUTER JOIN
          (
              SELECT
                  [dbo].[Medios_Pauta].[ID] AS [MedioId],
                  COALESCE(COUNT(*), 0) AS [regs2]
              FROM [dbo].[N_Preinscripcion]
  
              INNER JOIN
                  [dbo].[Medios_Pauta]
                  ON [dbo].[Medios_Pauta].[ID] = [dbo].[N_Preinscripcion].[MedioId] 
  
              INNER JOIN
                  [dbo].[N_Insc_Posg_Control] 
                  ON [dbo].[N_Insc_Posg_Control].[IdPre] = [dbo].[N_Preinscripcion].[ID]
  
              WHERE
                  ([dbo].[N_Preinscripcion].[Periodo] = @prd)
  
              GROUP BY
                  [dbo].[Medios_Pauta].[ID],
                  [dbo].[Medios_Pauta].[Nombre]
          ) [Inscritos_Posg]
          ON [Inscritos_Preg].[MedioId] = [Inscritos_Posg].[MedioId]   
  GROUP BY 
      [Inscritos_Preg].[MedioId],
      [Inscritos_Preg].[regs1],  
      [Inscritos_Posg].[MedioId],
      [Inscritos_Posg].[regs2]
  </code></pre>
  
  <p>And the result:</p>
  
  <pre><code>MedioId   regs1    regs2    Inscritos
  1         8        9        17
  4         75       16       91
  23        16       NULL     NULL
  24        2        NULL     NULL
  25        164      127      291
  28        8        NULL     NULL
  29        3        1        4
  30        1        2        3
  </code></pre>
  
  <p>How can I replace those NULL with a zero (0)</p>
  
  <p>Thanks!</p>