Practically speaking, is it good to use Self Join?

4

Regarding this question: What is a "self join" , technically speaking it is performative to do this, or use 1N, 2N and 3N (normal forms) is the best way?

Just remembering that I have never stated that one breaks rule of the other, it is purely about performatics between the two.

    
asked by anonymous 01.07.2014 / 15:16

1 answer

5

I do not think there's any difference in performance between making a join from one table to another, or from a table with itself. I have no data to support this answer, except for the lack of evidence to the contrary (could make benchmarks to prove this - in different DBMSs - but from a logical point of view it is the same thing one or the other).

(minto: there is a possibility that they will be more efficient than external joins - since there is only one set of metadata, indexes, and data itself to store in memory during a query But that's speculation, only benchmarks could actually prove that.)

If you are referring to implementing a tree structure using self join, it is not a good idea, but not because of the performance issue - but because it complicates too many queries (particularly if the tree is deep). But the queries you can do, should be as efficient as queries with joins to other tables.

But other uses should be ok. I just added a new answer to the linked question, giving another example of the practical use of the self join (in this case, a left outer join). Unfortunately I can not say much about his performance, because although I implemented this in a real system, it never had a large amount of data or access, so I do not know what your performance will be when it reaches large scale.

P.S. By the way, there is nothing in the self joins that violates in itself the normal forms. You may well have a standard template with references from a table to itself.

    
02.07.2014 / 00:41