How does Left Join / IS NULL eliminate records whi

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

in your example, the result would be EVERY record from t_left, as well as every matching record from t_right where they both have the same value. where there is no match, NULL values are given instead of values from t_right. where r.value is null simply eliminates the rows that did match, by detecting those null values.

essentially saying 'give me the rows from t_left that DONT match rows in t_right

a right join performs the reverse order of the left join.

Answer 2

Firstly a left join states that all records from the left table will be include, and only those from the right that match.

So lets say you have a red, blue and green sock in your drawer, and your friend only a red, yellow and blue, a left join from your drawer to your friends would return all the socks from your drawer (red, blue and green) and only matching ones from your friends (red and blue).

Now the IS NULL part says that you want to see all socks from your drawer, where there are not matching pair partners in your friends drawer, so from all your socks the only one with a missing pair is the green sock.

Answer 3

Left join gives all records from left table. If the any key present in left table is missing in right table all right table column will be null, but all the left table values will be present.

It is the basic difference between inner join and left / right join.

Thus when you do 'r.value = null` you get those records from left where there is no matching key in right table.

HTH.

Answer 4

LEFT JOIN takes the values on the left and tries to match the table on the left by the column specified on ON if it doesn't find a match then it interprets the right table's columns as NULL. hence only those that can't find a match on the right table will be selected by IS NULL operator

Answer 5

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)

Answer 6

 SELECT * FROM a LEFT JOIN b ON a.v = b.v ... WHERE b.id IS NULL

it's super simple logic:

• select from table "a"
• join table "b" (if matching rows exists - ON a.v=b.v otherwise use NULL's as values)
• WHERE condition: if something is missing (b.id IS NULL) = OK.