SQL INNER JOIN vs LEFT JOIN with a WHERE

Question

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.

How can a visual representation NOT help? Yes in your example they would be the same thing because when you add the predicates in your where clause you are only returning those rows that have a match in both tables. — Sean Lange, Jan 21 '16 at 20:06
You could also have "WHERE DetailTable.ParentID IS NOT NULL" and it would be the same. — GendoIkari, Jan 21 '16 at 20:07
@SeanLange a join can repeat data in the result set, which I don't think is represented by Venn diagrams well, IMHO — elmer007, Jan 21 '16 at 20:58
No data does not get repeated. What happens is when there are multiple rows in the second table that match the join predicates the values from the first table appear on both rows because that is the only logical thing to put in those rows. It is a bit confusing until you truly understand it. — Sean Lange, Jan 21 '16 at 21:11
@SeanLange that makes sense, but I don't think it's *exactly* what a Venn diagram shows, IMO. And by "the values from the first table appear on both rows," that's what I meant by repeated data- which is fine and useful, I just don't think that a Venn specifically conveys this possibility. Just a very picky opinion on my part, but still just an opinion — elmer007, Jan 21 '16 at 21:28
Now that you understand how that works look at the venn diagrams again. I would certainly be interested to find a better visual tool for describing joins if you come up with one. — Sean Lange, Jan 21 '16 at 21:40
[Venn-like diagrams for joins are unclear, unhelpful & misleading.](https://stackoverflow.com/a/55642928/3404097) [See my Q&A comments here.](https://stackoverflow.com/q/38549/3404097) All you have to do to see this is try to write a correct legend for one. Don't forget that SQL tables are bags not sets. Joins are on any condition & don't need constraints so at best the diagrams when explained address special cases and/or partial properties. (However, that a unique left or right input row can appear multiple times as an output subrow is not one of the problems for inner vs outer joins.) — philipxy, Feb 14 '23 at 04:02
Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Feb 14 '23 at 04:21
@philipxy I'm afraid not, sorry. It's similar in topic, but differs from the question asked here. — elmer007, Feb 14 '23 at 08:15

score 9 · Accepted Answer · edited May 23 '17 at 12:23

9

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.

edited May 23 '17 at 12:23

Community

1
1

answered Jan 21 '16 at 20:08

jpw

44,361
6
66
86

1

@JeffreyVanLaethem - Why are you doing `WHERE IS NULL`? That wasn't the question at all? This performs an anti semi join. like `NOT EXISTS` – Martin Smith Jan 21 '16 at 20:40
Re Venn diagrams for joins & differences between joins, see my comment on the question & at the question & answers at your own linked Venn Q&A--where at least for now we got most of the (pseudo-)Venns removed since they are mislabelled, misleading & unhelpful. – philipxy Feb 14 '23 at 04:29

score 1 · Answer 2 · answered Jan 21 '16 at 20:07

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.

score 0 · Answer 3 · answered Jan 21 '16 at 20:21

0

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

answered Jan 21 '16 at 20:21

paparazzo

44,497
23
105
176

1

Referencing the left in the where is fine, it is referencing the right hand table that turns it into an inner join (and assuming the reference is something that eliminates nulls of course) – Martin Smith Jan 21 '16 at 20:24
@MartinSmith One they are both referenced. In LEFT JOIN DetailTable I call DetailTable the left join as it is in that statement. I get that logically it could be the other way around. – paparazzo Jan 21 '16 at 20:29
2

Your terminology is wrong then. The left table is the one that appears to the left when reading the code from left to right. This is well established. e.g. [All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join](https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx) – Martin Smith Jan 21 '16 at 20:39

SQL INNER JOIN vs LEFT JOIN with a WHERE

3 Answers3

Linked