Question 183764 in Mysql Question posted in
The official MySQL documentation can be found here.

where statement breaks full join in MySQL

Consider the following tables:

create table `t1` (
  `date` date,
  `value` int
);

create table `t2` (
  `date` date,
  `value` int
);

insert into `t1` (`date`, `value`)
values ("2022-01-01", 1),
       ("2022-03-01", 3),
       ("2022-04-01", 4);
       
insert into `t2` (`date`, `value`)
values ("2022-01-01", 1),
       ("2022-02-01", 2),
       ("2022-04-01", 4);

The t1 table is missing 2022-02-01 date and t2 is missing 2022-03-01. I want to join these two tables so that it produces the following result:

| t1.date    | t1.value | t2.date    | t2.value |
|            |          |            |          |
| 2022-01-01 | 1        | 2022-01-01 | 1        |
| null       | null     | 2022-02-01 | 2        |
| 2022-03-01 | 3        | null       | null     |
| 2022-04-01 | 4        | 2022-04-01 | 4        |

The solution is a full join:

select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`

union

select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`;

Which produces exactly the result I want. But a where statement breaks everything:

select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`
where `t1`.`date` > "2022-01-01"

union

select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`
where `t1`.`date` > "2022-01-01";

I expected this result:

| t1.date    | t1.value | t2.date    | t2.value |
|            |          |            |          |
| null       | null     | 2022-02-01 | 2        |
| 2022-03-01 | 3        | null       | null     |
| 2022-04-01 | 4        | 2022-04-01 | 4        |

But I got this:

| t1.date    | t1.value | t2.date    | t2.value |
|            |          |            |          |
| 2022-03-01 | 3        | null       | null     |
| 2022-04-01 | 4        | 2022-04-01 | 4        |

I think I know what’s going on but I can’t find a way around it. The problem is that t1.date > "whatever" filters all empty rows in the t1 table. I’ve already tried this but it doesn’t work:

where `t1`.`date` > "2022-01-01" or `t1`.`date` = null
Tags:

3

Answers


  1. 0

    Seems like you should use t2.date > "2022-01-01" in the right join query.

    select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`
where `t1`.`date` > "2022-01-01"

union

select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`
where  `t2`.`date` > "2022-01-01";

    See demo at https://dbfiddle.uk/reo8UanD

    Login or Signup to reply.
  2. 0

    I’ve already tried this but it doesn’t work:

    where t1.date > "2022-01-01" or t1.date = null

    You should use

    where `t1`.`date` > "2022-01-01" or `t1`.`date` is null

    "NULL = NULL" results false as the NULL does not have value. Therefore it cannot be the same as any other value (even another NULL). The correct way is to use is null

    Login or Signup to reply.
  3. 0

    In MySQL to check if a value is null your not gonna use value = null but value IS NULL or value IS NOT NULL if you want to check when the value is assigned.

    As the result, you gonna have that:

    WHERE `t1`.`date` > "2022-01-01" OR `t1`.`date` IS NULL

    In MySQL is proper to write the instructions in uppercase.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search