Timeline for INNER JOIN vs LEFT JOIN performance in SQL Server
Current License: CC BY-SA 3.0
4 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 10, 2017 at 14:38 | comment | added | ripvlan | Yes and - the OUTER could cause a performance issue because it will cause all rows (more data) to be returned. Your assumption that the queries result in the same output is a fair one - however it isn't true in the general case and specific to each db design. And for those not 100% familiar with relational algebra could cause them grief. My point is only to offer more insight to people reading this looking for advice and that a LEFT/RIGHT won't magically solve a problem and could cause more issues. It's a power left for level 300 :-) | |
| Oct 9, 2017 at 16:19 | comment | added | MarredCheese | @ripvlan Of course, outer and inner joins aren't always interchangeable. The original question was about performance, which implies that we're talking about cases in which either join would return the same result set. | |
| Oct 9, 2017 at 15:18 | comment | added | ripvlan | Your statement about "outer-joined" is misleading and potentially incorrect. Outer means that the data on the other-side need not exist - and if it doesn't substitute NULL. Under specific circumstances the RDBMS may "skip" them (see above answer from dbenham). HOWEVER - outer vs inner can cause your query to return radically different results. INNER means - give results for which an item is in BOTH A & B. LEFT OUTER means all of A, and optionally B if it exists. First case - you get some rows, in second you get ALL rows. | |
| Mar 1, 2017 at 21:29 | history | answered | MarredCheese | CC BY-SA 3.0 |