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?

What do you mean with "it is not returning the data as expected"? What does it return and what do you expect? — a_horse_with_no_name
Unrelated, but: the concatenation is easier done with concat_ws(' ', ...) — a_horse_with_no_name
See that I have criteria for a ticker, KEXG. The top query does not return anything. But, if I remove the CASE statements, as in the second query, then it returns just fine. What issue do those CASE statements cause? Can I not do a NULL test on those values? — Landon Statis
Are you saying you get a different number of rows depending on what you put into the SELECT list? That sounds very strange. The CASE expressions do look fine to me though. Is it possible that any of the columns from zrec_investars_recommendation or all_recommendation_1 might be NULL? — a_horse_with_no_name
One of your columns is returning a NULL. When you concatenate a NULL into a string what you get out is a NULL. You are going to need to wrap every column you are concatenating in a COALESCE(theColumn, '') to insure that your NULL values don't nuke your concatenated string. — JNevill

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-04-22T13:55:14

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

Postgres Left Outer Join not working with NULLS

1 Answers