title: Why are those Null rows disappearing when I specify more than one value in an WHERE statement in PostreSQL?
body: <p>For privacy reasons I'm not allowed to post the exact code here but I'll give something similar...</p>
  
  <p>I have two subqueries that are left joined together. The first query just gets a list of dates</p>
  
  <pre><code>SELECT DATE
  FROM (
      SELECT (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7)::DATE + d.DATE AS DATE
      FROM generate_series(0, 99) AS d(DATE)
      WHERE (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7) + d.DATE BETWEEN (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7)
              AND (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 3)::DATE
      ) AS tr
  </code></pre>
  
  <p>Which returns </p>
  
  <pre><code>   date    
  ------------
   2018-01-14
   2018-01-15
   2018-01-16
   2018-01-17
   2018-01-18
  </code></pre>
  
  <p>Then I need to left join to another subquery to return value from those dates.</p>
  
  <p>Let's say this next table looks like</p>
  
  <pre><code> date           name    info
   2018-01-01     Alice     21
   2018-01-01     Brent     4
   2018-01-02     Alice     6
   2018-01-02     Brent     1
   2018-01-03     Brent     8
   2018-01-04     Brent     56
   2018-01-05    Brent      4
   2018-01-14    Alice     4
   2018-01-14    Brent     2
   2018-01-15    Alice     10
   2018-01-16    Alice     5
   2018-01-17    Brent     7
  </code></pre>
  
  <p>So I left joined both queries and specify which name I want</p>
  
  <pre><code>SELECT table1.DATE,
      table2.NAME,
      table2.info
  FROM (
      SELECT DATE
      FROM (
          SELECT (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7)::DATE + d.DATE AS DATE
          FROM generate_series(0, 99) AS d(DATE)
          WHERE (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7) + d.DATE BETWEEN (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 7
                          )
                  AND (NOW()::DATE - EXTRACT(DOW FROM NOW())::INT - 3)::DATE
          ) AS tr
      ) AS table1
  LEFT JOIN (
      SELECT NAME,
          DATE,
          info
      FROM lookup
      WHERE = 'Alice'
      ) AS TABLE 2
      ON table1.DATE = table2.DATE
  </code></pre>
  
  <p>Then I get something like</p>
  
  <pre><code>     2018-01-14    Alice     4
       2018-01-15    Alice     10
       2018-01-16    Alice     5
       2018-01-17    NULL      NULL
  </code></pre>
  
  <p>Similarly, specifying Brent returns</p>
  
  <pre><code>2018-01-14    Brent     2
   2018-01-15    NULL      NULL
   2018-01-16    NULL      NULL
   2018-01-17    Brent     7
  </code></pre>
  
  <p>But if I don't specify any name or have a WHERE in ('Alice', 'Brent'), I get </p>
  
  <pre><code> 2018-01-14    Alice     4
   2018-01-14    Brent     2
   2018-01-15    Alice     10
   2018-01-16    Alice     5
   2018-01-17    Brent     7
  </code></pre>
  
  <p>And all the Null rows dissapear. I need those NULL rows to be included in the analysis. I tried including NULLs with a where statement but that doesn't work. I'm aware that I can do a UNION ALL, but in the real case, there are just way too many names to go through. Is there another way to include all rows so it returns the following instead?</p>
  
  <pre><code> 2018-01-14    Alice     4
   2018-01-15    Alice     10
   2018-01-16    Alice     5
   2018-01-17    NULL      NULL 
   2018-01-14    Brent     2
   2018-01-15    NULL      NULL
   2018-01-16    NULL      NULL
   2018-01-17    Brent     7
  </code></pre>