Postgres Left Outer Join not working with NULLS

Question

I am trying to run the query below, but it is not returning the data as expected. I noticed that if I remove the references to the LEFT JOINed tables in the SELECT portion, it works fine. What am I doing wrong?

    SELECT m.ticker|| ' ' ||
           CASE
             WHEN x.ticker IS NULL THEN 'N'
             ELSE 'Y'
           END|| ' ' ||
           CASE
             WHEN s.ticker IS NULL THEN 'N'
             ELSE 'Y'
           END|| ' ' ||
           l.current_recommendation|| ' ' 
    ||TO_CHAR(l.recommendation_date,'MM/DD/YYYY')|| ' ' ||
           z.current_recommendation|| ' ' 
    ||TO_CHAR(z.date_recommended,'MM/DD/YYYY')
    FROM master_table m
    JOIN zrec_investars_recommendation l USING(ticker)
    JOIN all_recommendation_1 z USING(ticker)
    LEFT JOIN zer_ticker_data x ON x.ticker = m.ticker
    LEFT JOIN scr_tickers s ON x.ticker = s.ticker
    WHERE m.ticker = 'KEGX'
    ORDER BY CASE
      WHEN x.ticker IS NULL THEN 'N'
      ELSE 'Y'
   END DESC;

This however, works just fine:

   SELECT *
    FROM master_table m
    JOIN zrec_investars_recommendation l USING(ticker)
    JOIN all_recommendation_1 z USING(ticker)
    LEFT JOIN zer_ticker_data x ON x.ticker = m.ticker
    LEFT JOIN scr_tickers s ON x.ticker = s.ticker
    WHERE m.ticker = 'KEGX'
    ORDER BY CASE
      WHEN x.ticker IS NULL THEN 'N'
      ELSE 'Y'
    END DESC;

Why can't I perform the NULL tests in the SELECT portion? Is there a way to fix this?

Answer 1

I am pretty sure this has nothing to do with the CASE expressions, but some of the other columns might be null. And something||NULL returns NULL so the whole expression yields null.

You can easily verify this by running select m.ticker, <your expression here> from .... Then you will see, that the rows returned are the same.

You can use concat_ws() instead, which treats NULL like an empty string. Plus it makes the whole thing easier to read:

SELECT concat_ws(' ', m.ticker,
                      CASE
                        WHEN x.ticker IS NULL THEN 'N'
                        ELSE 'Y'
                      END,
                      CASE
                        WHEN s.ticker IS NULL THEN 'N'
                        ELSE 'Y'
                      END,
                      l.current_recommendation, 
                      to_char(l.recommendation_date,'MM/DD/YYYY'), 
                      z.current_recommendation, 
                      to_char(z.date_recommended,'MM/DD/YYYY')
                 )
FROM ...

---

If you can accept true or false instead of Y and N, you can simplify the CASE expressions to: (x.ticker IS NULL)::text