Luis Moran Luis Moran - 10 days ago 5
MySQL Question

Unable to see null values in left join MySQL

I have 2 tables that I need to join but for some reason I am not getting the expected result.

Table 1 has all dates of the year and table 2 has data for some dates. I have the following:

Table 1

+------------+
| dates |
+------------+
| 2016-11-01 |
| 2016-11-02 |
| 2016-11-03 |
| 2016-11-04 |
| 2016-11-05 |
| 2016-11-06 |
| 2016-11-07 |
| 2016-11-08 |
| 2016-11-09 |
| 2016-11-10 |
+------------+


Table 2

+------------+--------+----+
| dates | status | id |
+------------+--------+----+
| 2016-11-01 | 1 | 1 |
| 2016-11-02 | 1 | 1 |
| 2016-11-03 | 1 | 1 |
| 2016-11-04 | 1 | 2 |
| 2016-11-05 | 1 | 2 |
| 2016-11-06 | 1 | 2 |
| 2016-11-07 | 1 | 1 |
| 2016-11-08 | 1 | 2 |
| 2016-11-09 | 1 | 1 |
| 2016-11-10 | 1 | 1 |
+------------+--------+----+


Expected result

+------------+--------+
| dates | Status |
+------------+--------+
| 2016-11-01 | 1 |
| 2016-11-02 | 1 |
| 2016-11-03 | 1 |
| 2016-11-04 | null |
| 2016-11-05 | null |
| 2016-11-06 | null |
| 2016-11-07 | 1 |
| 2016-11-08 | null |
| 2016-11-09 | 1 |
| 2016-11-10 | 1 |
+------------+--------+


Current Result:

+------------+--------+
| dates | status |
+------------+--------+
| 2016-11-01 | 1 |
| 2016-11-02 | 1 |
| 2016-11-03 | 1 |
| 2016-11-07 | 1 |
| 2016-11-09 | 1 |
| 2016-11-10 | 1 |
+------------+--------+


this is the query I am currently using:

select
a.dates,
b.status
from table1 a
left join table2 b on a.dates = b.dates
where b.id = 1;


Unfortunately it only shows data where the id is 1 and skips the null values. I need to see the null values too. What I am doing wrong???

Answer

You need to move the b.id = 1 criterion from the where clause to the join condition because the where clause applies to the entire resultset after the join:

select 
    a.dates,
    b.status
from table1 a 
left join table2 b on a.dates = b.dates and b.id = 1
Comments