SQL INNER JOIN vs LEFT JOIN with a WHERE

Question

I am trying to grasp SQL joins more intuitively. Just yesterday, I learned about how a RIGHT JOIN can just be re-written as a LEFT JOIN (by flipping the order of the tables), which helped me understand much better the way that the two joins work.

However, now I'm wondering if an INNER JOIN could be re-written as a LEFT JOIN with a WHERE condition- meaning that their logic could be equivalent (by "logic" I do not mean the execution plan, but the way that the intended result set would be described).

Like:

SELECT * FROM HeaderTable
INNER JOIN DetailTable 
ON HeaderTable.ID = DetailTable.ParentID

Which I would read as "Show me all the records from tables HeaderTable and DetailTable that have a matching value in the HeaderTable.ID and DetailTable.ParentID fields." Being the same as:

SELECT * FROM HeaderTable
LEFT JOIN DetailTable 
ON    HeaderTable.ID = DetailTable.ParentID
WHERE HeaderTable.ID = DetailTable.ParentID

Which I would read as "Show me all the records from tables HeaderTable and DetailTable where the value of HeaderTable.ID is the same as the value of DetailTable.ParentID."

Will these return the same result set? I am more asking about the logic being the same as opposed to one being more efficient than the other.

If I may ask, please don't answer with any Venn diagrams as these don't seem to describe the logic of a join exactly to me.

Answer 1

Yes, they will return the same result. The left join without the where clause would read as show me all the records from the header table and the related items from the details table or null for the details where there are no matches.

Adding a where clause relating the ids effectively transforms the left join to an inner join by eliminating the non-matching rows that would have shown up as having null for the detail part.

In some databases, like MS SQL Server, the left join would show up as an inner join in the query execution plan.

Although you stated that you don't want Venn diagrams I can't help referring you to this question and its answers even though they are filled with (in my opinion very helpful) Venn diagrams.