I already know what a self-join does. Thank you, I also read all the other computerised operational descriptions on stack overflow, so I know this is not actually a duplicate question, so please do not give me tables or join lists.
What I am seeking to why it would be done (and please, not just the self-referencing employee-manager example).
In plain English, what would I seek to achieve from a self join?
My usage is in a university course, and coming from a Relational Algebra angle. I have done some SQL for a few years but the instructor loves to do self-joins on tables (after renaming one or more fields). Not something often done in SQL, so I’m wondering what the action is that he is trying to perform but he seems pretty keen about doing it frequently.
I thought I’d ask here, as many others have asked for this information but get marked as “already answered” but all the answers give operational descriptions NOT the “why is this being done”.
Advertisement
Answer
The reason why the employee-manager example is so common, is because it hits the nail on the head. A self join on a table looks for pairs of rows, like any join, but with both rows coming from the same table. Nothing special really.