Search code examples
mysqlleft-join

In LEFT JOIN, why did AND is not filtering out row in LEFT TABLE

Currently using MySQL 8.0

I want to keep only the rows in the control group in the left table.

My dummy data is:

CREATE table test1 (id int, ex_group varchar(50));
INSERT INTO test1 VALUES
(1, 'control'),
(2, 'control'),
(3, 'test'),
(4, 'test');
create table test2 (id int, gender varchar(50));
insert into test2 values 
(1,'f'),
(2,'f'),
(3,'m');

My query is:

select test1.*, test2.*
from test1
left join test2
on test1.id = test2.id
and test1.ex_group = 'control'

But the result is not what I expected because the 'test' is not filtered out in LEFT TABLE:

enter image description here

I understand I can use WHERE to filter out in LEFT TABLE, but I do not understand is why the filtering with AND works for RIGHT TABLE but not for LEFT TABLE.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e9398a8ebd9b5d96f1253c92827c4bc0

Solution

  • Use where instead of condition in left join

    select test1.*, test2.*
from test1
left join test2
on test1.id = test2.id
WHERE test1.ex_group = 'control'