How does Left Join / IS NULL eliminate records which are there in one table and not in the other?

Question

I am having a tough time to understand why does LEFT JOIN / IS NULL eliminate records which are there in one table and not in the other. Here is an example

SELECT  l.id, l.value
FROM    t_left l
LEFT JOIN t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

Why should r.value = NULL eliminate records ? I am not understanding . I know I am missing something very basic but at present I cant figure out even that basic one. I would appreciate if someone explains it to me in detail .

I want a very basic explanation.

Answer 1

This could be explained with the following

mysql> select * from table1 ;
+------+------+
| id   | val  |
+------+------+
|    1 |   10 |
|    2 |   30 |
|    3 |   40 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from table2 ;
+------+------+
| id   | t1id |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

Here table1.id <-> table2.t1id

Now when we do a left join with the joining key and if the left table is table1 then it will get all the data from table1 and in non-matching record on table2 will be set to null

mysql> select t1.* , t2.t1id from table1 t1 
left join table2 t2 on t2.t1id = t1.id ;
+------+------+------+
| id   | val  | t1id |
+------+------+------+
|    1 |   10 |    1 |
|    2 |   30 |    2 |
|    3 |   40 | NULL |
+------+------+------+

3 rows in set (0.00 sec)

See that table1.id = 3 does not have a value in table2 so its set as null When you apply the where condition it will do further filtering

mysql> select t1.* , t2.t1id from table1 t1 
left join table2 t2 on t2.t1id = t1.id where t2.t1id is null;
+------+------+------+
| id   | val  | t1id |
+------+------+------+
|    3 |   40 | NULL |
+------+------+------+
1 row in set (0.00 sec)