Home Why am I getting null values on the matched variables in a LEFT JOIN in SQL?

Questions

Why am I getting null values on the matched variables in a LEFT JOIN in SQL?

May 16, 2022

I am trying to left join a table that includes all possible dates with a table that has information for an id on a given date, but has gaps in the dates. The code below will produce example tables.

CREATE TABLE example_info (
    info_id varchar(255),
    info_date date,
    change_in numeric,
    min_in numeric,
    max_in numeric
);

INSERT INTO example_info (info_id, info_date, change_in, min_in, max_in)
VALUES  ('1001-abc', '2020-02-01', '45', '45', '90'),
        ('1001-abc', '2020-02-03', '40', '50', '90'),
        ('3007-ghx', '2020-02-02', '10', '30', '40');

CREATE TABLE date_info (
    info_id varchar(255),
    info_date date
);

INSERT INTO date_info (info_id, info_date)
VALUES  ('1001-abc', '2020-02-01'),
        ('1001-abc', '2020-02-02'),
        ('1001-abc', '2020-02-03'),
        ('3007-ghx', '2020-02-01'),
        ('3007-ghx', '2020-02-02'),
        ('3007-ghx', '2020-02-03');

The left join result that I desire would be:

info_id   | info_date  | change_in | min_in | max_in |
-------------------------------------------------------
1001-abc  | 2020-02-01 |    45     |   45   |   90   |
1001-abc  | 2020-02-02 |    NULL   |   NULL |   NULL |
1001-abc  | 2020-02-03 |    40     |   50   |   90   |
3007-ghx  | 2020-02-01 |    NULL   |   NULL |   NULL |
3007-ghx  | 2020-02-02 |    10     |   30   |   40   |
3007-ghx  | 2020-02-03 |    NULL   |   NULL |   NULL |

However, using the following code provides the following result, and I do not understand why:

SELECT  ei."info_id",
        di."info_date",
        ei."change_in",
        ei."min_in",
        ei."max_in"
FROM date_info di
LEFT JOIN
example_info ei
ON di."info_id" = ei."info_id"
AND di."info_date" = ei."info_date";

info_id   | info_date  | change_in | min_in | max_in |
-------------------------------------------------------
1001-abc  | 2020-02-01 |    45     |   45   |   90   |
NULL      | 2020-02-02 |    NULL   |   NULL |   NULL |
1001-abc  | 2020-02-03 |    40     |   50   |   90   |
NULL      | 2020-02-01 |    NULL   |   NULL |   NULL |
3007-ghx  | 2020-02-02 |    10     |   30   |   40   |
NULL      | 2020-02-03 |    NULL   |   NULL |   NULL |

>Solution :

Almost a typo, but you should be selecting the info_id from the date_info table:

SELECT di.info_id,  -- change is here
       di.info_date,
       ei.change_in,
       ei.min_in,
       ei.max_in
FROM date_info di
LEFT JOIN example_info ei
    ON di.info_id = ei.info_id AND
       di.info_date = ei.info_date;

In your current query, selecting ei.info will always be null for those records in date_info which did not match to any records in the right table of the join.