CODEWITHSUNDEEP
sql-serverjoinleft-joininner-join
elmer007
elmer007

Reputation: 1445

SQL INNER JOIN vs LEFT JOIN with a WHERE

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.

Upvotes: 9

Views: 7408

Answers (3)

jpw
jpw

Reputation: 44921

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.

Upvotes: 10

paparazzo
paparazzo

Reputation: 45106

On a left join if you reference the left in the where then you negate the left and turn it into regular join

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112762

Yes they would return the same result.

But then you could simply write

SELECT * 
FROM HeaderTable, DetailTable
WHERE HeaderTable.ID = DetailTable.ParentID

this returns the same result as well. This is an old syntax used before the join-clauses were introduced.

Upvotes: 1

Related Questions