Search code examples
sqljoinleft-joininner-join

Conceptual question: Does left join with not null filter on the joining key always equal to inner join?

This is a conceptual question. So I dont have any actual code. But it is simple to follow.

Lets stay we have two tables (Join_1 and Join_2) from joining A and B:

Join_1:

Select *
From A join B on A.id = B.id

Join_2

Select *
From A left join B on A.id = B.id
where B.id is not null

Question: Does Join_1 always equal to Join_2? You can think of any conditions such as null values, duplicates and so on.

Solution

  • For all practical purposes, "yes".

    The only exception would be if a.id could be NULL. In that case, the first version would filter out those rows. The second would include them.