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.

I am trying to grasp SQL joins more intuitively. For example, learning how a RIGHT JOIN can just be re-written as a LEFT JOIN (by flipping the order of the tables) 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.

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.

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.