-2

Suppose I have a self join query whose results are precisely the same for an inner join as they are for an outer join. In that case, is one more performant than another, or does it vary by what the query optimizer does?

Typically I'd expect a LOJ to be less performant as it has to do all the work of an INNER JOIN plus the extra work of null-extending the results. But if that extra work isn't needed, should performance in theory be the same? I've tried this myself, but haven't noticed any consistent perf differences, but wanted to ask here to be sure.

jfalkson
  • 3,471
  • 4
  • 20
  • 25

2 Answers2

2

The correct way to answer your question is by running the queries on your data on your system.

Often, the effect of the outer join is simply to reduce the choices the optimizer can make. If this is the case, then essentially the same query plan might be used -- and the queries are identical

Of course, the effect could be much more dramatic. The resulting query plan could be much worse with the outer join.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

What theory? The theory for the notation does not have anything to say about performance. The "theory" for some hypothetical implemention depends on that hypothetical implementation, which can roughly be divided into logical (query rewrite) & both explicit (indexes) & implicit (other) physical (implementation) aspects. The reality is coarsly guided by those aspects (guaranteed or otherwise) of an actual implementation. Ultimately we must measure for estimated/typical/actual loads & data--performance is chaotic, a small change in circumstances can make a large change in results.

The whole thing is moot though since if you declare constraints & you know the inner & outer joins are the same then the DBMS is extremely likely to also know and if not then extremely likely to do the same amount of work anyway.

Moreover the sort of difference involved is going to be insignificant compared to whatever the actual work getting done is. Entire books await re optimization in general and for DBMSs specifically.

Give arguments to support the following conjectures:
• if n>=10, the time required to initialize a matrix is not very important
• if n<10, the time required to initialize a matrix is not very important
-- Points to Ponder 1.1, Chapter 1 (Introduction), re Rule 1, "Write clearly--don't be too clever", the classic, The Elements of Programming Style by Kernihan & Plauger (1974)

philipxy
  • 14,867
  • 6
  • 39
  • 83