LEFT JOIN issue in Mysql

Applications Programming
1

I have a data table as follows:

mysql> select * from validations where source = "a03";
+------------+-------+--------+
| date       | price | source |
+------------+-------+--------+
| 2001-01-03 |    80 | a03    |
| 2001-01-04 |    82 | a03    |
| 2001-01-05 |    84 | a03    |
| 2001-01-06 |    86 | a03    |
| 2001-01-07 |    88 | a03    |
| 2001-01-08 |    90 | a03    |
+------------+-------+--------+

And some dates as follows:

mysql> select * from dates;
+------------+
| date       |
+------------+
| 2001-01-01 |
| 2001-01-02 |
| 2001-01-03 |
| 2001-01-04 |
| 2001-01-05 |
| 2001-01-06 |
| 2001-01-07 |
+------------+

In a left join the result is the following:

mysql> SELECT dt.date, v.price, v.source FROM dates dt LEFT JOIN validations v ON dt.date = v.date WHERE source = "a03";
+------------+-------+--------+
| date       | price | source |
+------------+-------+--------+
| 2001-01-03 |    80 | a03    |
| 2001-01-04 |    82 | a03    |
| 2001-01-05 |    84 | a03    |
| 2001-01-06 |    86 | a03    |
| 2001-01-07 |    88 | a03    |
+------------+-------+--------+

Why are the dates first and second of january not returned?

2

It would be, except your where clause removed it from the result:

i.e...

SQL> SELECT dt.c1, v.price, v.source FROM za2 dt LEFT JOIN za1 v on dt.c1 = v.c1;

        C1      PRICE SOURCE
---------- ---------- ----------
         1         10 a03
         2         10 a03
         3

SQL> SELECT dt.c1, v.price, v.source FROM za2 dt LEFT JOIN za1 v on dt.c1 = v.c1 where source='a03';

        C1      PRICE SOURCE
---------- ---------- ----------
         1         10 a03
         2         10 a03
3

Yes, that was pretty elementary. Should have been:

SELECT dt.date, v.price, v.source FROM dates dt LEFT JOIN validations v ON dt.date = v.date WHERE source IS NULL OR source = "a03";