Left Join returns NULL on matching data

Question

I perform simple LEFT JOIN between two tables:

A:

YR  QTR MTH DAY        DEPT      SALES
2017 2  04  2017-04-01 B         xxxxxx
2017 1  03  2017-03-31 A         xxxxxxxx
2017 1  03  2017-03-31 B         xxxxx
2017 1  03  2017-03-30 A         xxxx

Second table (B) I use to bring QTR_ALT number

YEAR MONTH QTR QTR_ALT
2016 12    4   12
2017 01    1   12
2017 02    1   12
2017 03    1   11
2017 04    2   11

Following LEFT JOIN B ON A.YR = B.YEAR AND A.QTR = B.QTR AND A.MTH=B.MONTH returns NULL for QTR_ALT for A.DAY BETWEEN '2016-12-01' AND '2017-03-31'

YR   QTR QTR_ALT  MTH DAY         DEPT      SALES
2017 2   11       04  2017-04-02  A         xxxxxx
2017 2   11       04  2017-04-01  A         xxxxxx
2017 2   11       04  2017-04-01  B         xxxxxx
2017 1   NULL     03  2017-03-31  A         xxxxxxxx
2017 1   NULL     03  2017-03-31  B         xxxxx
2017 1   NULL     03  2017-03-30  A         xxxx

I tried moving WHERE condition to JOIN but no luck. How is it possible these dates don't get join even though corresponding record exists in table B?

Full code:


    SELECT YEAR(A.DAY) as YR, 
               QUARTER(A.DAY) as QTR, 
               B.QTR_ALT, 
               (REPEAT(0, 2-LENGTH(MONTH(A.DAY))) || MONTH(A.DAY)) MTH,
               A.DAY, 
               A.DEPT, 
               SUM(A.VAL) as SALES

                FROM A
                LEFT JOIN (SELECT  TO_CHAR(ADD_MONTHS(a.DT, - b.Y), 'YYYY') as YEAR,
                                    TO_CHAR(ADD_MONTHS(a.DT, - b.Y), 'MM') as MONTH,
                                    CEIL(TO_NUMBER(TO_CHAR(add_months(a.dt, -b.y), 'MM')) / 3) as QTR,
                                    CEIL(b.y/3) as QTR_ALT

                                    FROM (SELECT TRUNC(CURRENT_DATE, 'MONTH') as DT) a
                                            CROSS JOIN (SELECT SEQ8()+1 as Y FROM TABLE(GENERATOR(ROWCOUNT => 36)) ORDER BY 1) b
                                    ORDER BY YEAR, MONTH)  B

                    ON QUARTER(A.DAY) = B.QTR 
                    AND (REPEAT(0, 2-LENGTH(MONTH(A.DAY))) || MONTH(A.DAY)) = B.MONTH


    WHERE (YEAR(A.DAY) = B.YEAR)
    AND (A.DAY BETWEEN '2016-12-01' AND '2017-03-31')
    AND A.DEPT in  ('A', 'B')

    GROUP BY A.DAY, YEAR(A.DAY),QUARTER(A.DAY),B.QTR_ALT,(REPEAT(0, 2-LENGTH(MONTH(A.DAY))) || MONTH(A.DAY)), DEPT
    ORDER BY A.DAY DESC

LEFT JOIN B ON A.QTR = B.QTR AND A.MTH=B.MONTH WHERE A.YR = B.YEAR — Mehrnoosh, Dec 13 '19 at 09:10
I can't [reproduce](https://rextester.com/TBNV86398) your issue. — HoneyBadger, Dec 13 '19 at 09:13
You should try to simplify your problem down so that your code example is easier to read. You said that your join is "simple" but it isn't - you have a left join with an inner sql query which has itself got a crossjoin inside another subquery which contains a generator... I find that by doing this I often solve my own issue anyway. — Simon D, Dec 13 '19 at 09:45
@SimonD - I moved the `B` table to a view and it didn't fix the issue. Still I don't understand why my issue didn't occur for same date range in 2017/2018 — marcin2x4, Dec 13 '19 at 10:03
Note that SEQ8() can have gaps -- use ROW_NUMBER() for gap-free sequences. Also try LPAD() for more concise leading zeroes. — waldente, Dec 13 '19 at 13:49
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Dec 14 '19 at 07:34
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After an OUTER JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS ORDER BY without TOP/LIMIT is a NOOP. — philipxy, Dec 14 '19 at 07:35
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. PS This code is clearly not minimal. (Always find the first subexpression that doesn't return what you expect.) — philipxy, Dec 14 '19 at 07:35

score 1 · Accepted Answer · answered Dec 14 '19 at 05:24

1

CREATE TEMP TABLE A (yr number, qtr number, mth text, day date, dept text, sales number);
INSERT INTO A values (2017,2,'04','2017-04-01','B', 10), 
    (2017,1,'03','2017-03-31','A', 11), 
    (2017,1,'03','2017-03-31','B', 20),
    (2017,2,'03','2017-03-30','A', 6);


WITH 
sub_b AS (
    SELECT 
        TRUNC(CURRENT_DATE, 'MONTH') AS dt,
        SEQ8() AS s, 
        ROW_NUMBER() OVER (ORDER BY s) AS y,
        ADD_MONTHS(dt, - y) as tmp_date,
        TO_CHAR(tmp_date, 'YYYY') AS year,
        --TO_CHAR(tmp_date, 'MM') AS month, -- NOT USED
        --QUARTER(tmp_date) as QTR, -- NOT USED
        CEIL(y/3) as qtr_alt -- this value seems broken
    FROM TABLE(GENERATOR(ROWCOUNT => 36))
)
SELECT a.yr, 
    a.qtr, 
    b.qtr_alt, 
    a.mth,
    a.day, 
    a.dept, 
    SUM(a.sales) AS sales
FROM a
LEFT JOIN sub_b AS b
    ON a.yr = b.year AND date_trunc('month',a.day) = b.tmp_date
WHERE a.day BETWEEN '2016-12-01' AND '2017-03-31'
    AND a.dept in ('A', 'B')
GROUP BY 1,2,3,4,5,6
ORDER BY a.day DESC;

seems to work as given how I read your code/intent.

answered Dec 14 '19 at 05:24

Simeon Pilgrim

22,906
3
32
45

Thanks Simeon! The issue of `NULL` I'm experiencing appears when full range of data is present. In my opinion it seems that snowflake runs both tables simultaneously but since it finishes table scan on first table, he stops second table scan resulting in not generating mapping for latter periods. – marcin2x4 Dec 16 '19 at 07:06
1

if you join between the tables correctly you should not be able to get nulls in the QTR_ALT version. – Simeon Pilgrim Dec 18 '19 at 03:28
You version of 'quarter calculator' sub-query seems to be working perfectly! – marcin2x4 Dec 18 '19 at 08:53
In my original query if you change `ROWCOUNT` to 40, issue gets resolved also . It seems as if snowflake stops executing subquery when finishes with first table. – marcin2x4 Dec 18 '19 at 09:30

Left Join returns NULL on matching data

1 Answers1