Sql left join on left key with null values

Question

I have a question about join with key with null value.

Suppose I have a table t, which is going to be on left side. (id is primary key and sub_id is the key to join with the right table.)

    id sub_id value
    1  3       23
    2  3       234
    3  2       245
    4  1       12
    5  null    948
    6  2       45
    7  null    12

and I have another table m which is on right side. (t.sub_id = m.id)

    id feature
    1  9       
    2  8       
    3  2       
    4  1       
    5  4    
    6  2       
    7  null

Now I want to use

    select * from t left join m on t.sub_id = m.id   

What result will it return? Is Null value in left key influence the result? I want all null left key rows not to shown in my result.

Thank you!

Answer 1

A left join is quite simple. It keeps all rows in the first (left) table plus all rows in the second (right) table, when the on clause evaluates to "true".

When the on clause evaluates to "false" or NULL, the left join still keeps all rows in the first table with NULL values for the second table.

If either sub_id or id is NULL, then your on clause evaluates to NULL, so it keeps all rows in the first table with NULL placeholders for the columns in the second.

Answer 2

Left joins by definition will include all the ids from the left part of the join, no matter if the id has a valid mapping in the other table, if it has not it will be related to NULL.

If you want to use only valid links you need to use INNER JOIN.

Answer 3

I think inner join is not a solution because there are keys in the right and he doesn't want to have it in the joined table. You can still do left join then add a statement to drop all keys = null

you can try this

SELECT * 
FROM t 
LEFT JOIN m ON t.sub_id = m.id
WHERE t.sub_id != NULL

or

SELECT * 
FROM t 
LEFT JOIN m ON t.sub_id = m.id  
WHERE t.sub_id IS NOT NULL