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.

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

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

6

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

43,521
6
61
82

I just ran some tests comparing the two methods in SQL Server and the left join with a where is showing as a left outer join in the query plan. – Jeffrey Van Laethem Jan 21 '16 at 20:13
1

@JeffreyVanLaethem Interesting. It correctly transforms to an inner join in my MSSQL 2012 instance. What version are you running? See this image for an example: http://ibin.co/2UG1rsiYGfDR – jpw Jan 21 '16 at 20:14
Huh. My guess is it will vary depending on a great many things. This is terrible, because now I need to know why >_ – Jeffrey Van Laethem Jan 21 '16 at 20:16
I'm running 2014 (12.0.4213.0). I also tried with the same database restored to a 2008 r2 server, got slightly different plan, but still left join. here's the plan from 2014: http://ibin.co/2UG7SkvA3fQn – Jeffrey Van Laethem Jan 21 '16 at 20:37
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
@JeffreyVanLaethem The query you are running is not equivalent to the one in the question and it would not be transformed to an inner join. – jpw Jan 21 '16 at 20:42
Whoops, I misread the question in haste. False alarm. @jpw is 100% right. – Jeffrey Van Laethem Jan 21 '16 at 21:12
I like the explanation in this answer. Just to check, though- could it be said that they will return the same result *always*? As in there is no condition where they are not interchangeable? – elmer007 Jan 21 '16 at 23:07
1

@elmer007 Yes, that would be true. The two queries presented in the question should never return different results - they are equivalent (in result, possibly not in how they are executed - that would depend on the query parser/optimizer). – jpw Jan 21 '16 at 23:10

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

43,659
20
99
164

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
1

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