1

I read some existing codes and found out all SQLs wrote as outer join even it is inner join. Then, I start to think about the performance difference between if the result are same. In my mind, inner join should be faster than outer join. But, I can't find any document to prove this. Could you help me on this? Thanks

Hongtao
  • 197
  • 1
  • 5
  • Can you show an example of an inner join rewritten as an outer join? – mclaassen Aug 04 '14 at 21:19
  • If the results are the same (i.e. all keys match in the two tables), there is no reason to believe that one would be faster or slower than the other. – Gordon Linoff Aug 04 '14 at 21:26
  • Here is a wealth of good information: [Clicky!](http://stackoverflow.com/a/2726683/394028) – Andrew Aug 04 '14 at 21:53
  • @GordonLinoff: It could. It depends on the circumstances (what SQL engine is used, what indexes are available, what metrics are available to the optimizer (how it decides to perform the join), and so on). In e.g. `a join b on a.x=b.x` the engine can choose to iterate over `a` and look up matches on a `b.x` index or it could iterate over `b` and look up matches on an `a.x` index. But in `a left join b on a.x=b.x` it can only do the former without having to perform additional logic to build the result. – 500 - Internal Server Error Aug 04 '14 at 22:27
  • This is RDBMS specific. Please specify one. – usr Aug 04 '14 at 22:36
  • @500-InternalServerError . . . I don't think my point was clear. Without more information, either join could be faster or slower than the other. There is no reason to think they would be the same or different. – Gordon Linoff Aug 05 '14 at 01:10

2 Answers2

0

I am not sure, but according to this(www.w3schools.com/sql/sql_join_full.asp) FULL OUTER JOIN returns all rows from the left table and right table while inner join(www.w3schools.com/sql/sql_join_full.asp) selects all rows from both tables as long as there is a match. So with inner join not only haves to go through both tables now it must make a check. So I would think inner join takes longer to build its list of results. However, memory is another issue. Inner join most likely returns less things sometimes (depending on what the criteria is) while full outer join will return both tables, which means more data on memory.

But it might take less time for inner join in comparison to left outer join or right outer join because those outer joins also have to match.(http://www.techonthenet.com/sql/joins.php)

Rika
  • 768
  • 1
  • 5
  • 16
-1

It's actually somewhat of a complicated question to answer. Yes, there can be a difference in performance when INNER JOINS are written as OUTER JOINS. But I'll read into your question a bit and re-ask it a bit differently:

"Why do I see people writing INNER JOINs as OUTER JOINS?"

People use this technique frequently when creating views. In a view, you don't necessarily know what tables the user of the view wants -- they may want less than all of the columns. In cases when users don't want all of the columns, they may, in fact, not want whole tables. Consider a schema such as a star schema, where many "small" dimension tables reference a single "fact" table. As an example, let's say there's a table called Sales, which has a bunch of other related tables like Date, Salesperson, and Territory. You create a view which "flattens this out" by joining all of the tables to the fact tables.

If you used INNER JOINs, every time someone used the view, the database server would often generate a query plan which referenced every table in the view. So even if you wrote a query like "SELECT TerritoryName, SUM(Sales) from vw_AllSales", an INNER JOIN based view would still query Date and Salesperson. Using an OUTER JOIN in the view definition will often let the optimizer "optimize away" references to tables that the view consumer doesn't care about.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170