I am wondering about this since self joins only require one table so it feels like it wouldn't make a difference. I have read this question: Explanation of self-joins. There are several answers and they use different types of joins for seemingly the same task.
So does it make a difference or not? If so, can you show an example of how?
It all depends on what you want to do with the data. This answer does a great job of detailing what a self inner join might look like. I recently wrote a report that required comparing grades from two courses a student took in succession. It went something like this:
Given a table student_course:
STUDENT_ID COURSE GRADE
1 MTH251 A
1 MTH252 B
2 MTH251 A
2 MTH252 A
3 MTH251 B
3 MTH252 C
Query:
SELECT course1.student_id
, course1.course AS course1
, course1.grade AS grade1
, course2.course AS course2
, course2.grade AS grade2
FROM student_course course1
INNER JOIN student_course course2
ON course1.student_id = course2.student_id
WHERE course1.course = 'MTH251'
AND course2.course = 'MTH252';
Fiddle here. Sorry, the PostgreSQL fiddle wasn't working for me so I used Oracle for testing. The PostgreSQL equivalent should look roughly the same.
Now say I wanted to see a student who may not have taken MTH252. You could do this:
SELECT course1.student_id
, course1.course AS course1
, course1.grade AS grade1
, course2.course AS course2
, course2.grade AS grade2
FROM student_course course1
LEFT OUTER JOIN student_course course2
ON course1.student_id = course2.student_id
AND course2.course = 'MTH252'
WHERE course1.course = 'MTH251';
The former displays students who have taken BOTH MTH251 and MTH252, and the latter shows students who have taken MTH251, regardless of their completion of MTH252.
As noted by Nick.McDermaid, a self join works exactly like joining two tables with different data.
It does indeed make a difference. There are many ways to think about it conceptually.
In a way,a join means you want to utilize one row instead of two rows, if possible. You are basically taking two tables, and making one single table out of them.
The best way I think to understand between inner, right/left, and outer is tables
**FULL Outer:**
name number
john
jamie 7
ann 10
11
12
Some of the rows are missing elements, because an outer join uses every single possible row of every table. In this case, whatever we chose to be our linker (ie what comes after the "ON"), John has a linker-value that does not correspond to any row in our second table. And 11 and 12 are numbers in the second table that don't have linker-values that match up with a name in the other name
Inner means that if one of the elements of either of your tables does not have a data member in common with the other table, then we must skip over those elements. So the table becomes
**INNER**
name number
jamie 7
ann 10
Left/right joins are the same thing if you consider them from an abstract point of view, because each of these joins will cause one of the tables to have its full set of elements displayed, while the other is limited to only those which have a partner in the other table. Left/right are outer joins, but basically only half-outer.
**left/right:**
name number
lee
john
jamie 7
ann 10
name number
jamie 7
ann 10
15
29
In Explanation of self-joins take the inner join example he gave. But what if there are some Bosses who list an employee who cannot be found in the employee table, or even list a null value? And what if there is an employee who who in the employee table, lists a boss, but the boss is not present in the boss table. Or maybe the employee lists no boss at all? (That one would actually be realistic, since some people are self-employed)
Then we must decide, what exactly are we trying to query for? Do we need to account for self-employed employees? If so, INNER JOIN is ruled out. So now we must decide if we wish to include bosses who do not have employees among our pool in the database.
Thinking realistically, I can imagine we would do a left or right join.
First of all, left join is an outer join.
It does make a difference as definition of inner join and left join will be same while implementing self join also. So lets say you have a typical Employee table with manager. Now for the sake of argument lets say that one of the employee's manager is not in the employee list. So if you do a typical self join, you will not get that record. But with the help of left join you can get that record returned.
Also one of the use of left join is to find the records which is in left table but not in right one, by using where clause as where right_table.key is null. You can achieve the same for self join as well, which you can't do with inner join.
Here are a few queries explaining above scenario and you can see the extra things you can do with left join but not with inner join.
LEFT (OUTER) JOIN ON by definition gives the rows that INNER JOIN ON gives plus unmatched rows on the left extended by NULLs. So if every left row is matched then they give the same answer. In particular if the ON condition is equality of a non-NULL FK (foreign key) of the left table referencing a PK (primary key) or a UNIQUE NOT NULL in the other then every left row has a match and they give the same answer. The same goes for RIGHT JOIN & the right table.
So in LEFT self-JOIN if every left row is matched then they give the same answer. And in particular if the ON condition is equality of a non-NULL FK (foreign key) of it referencing its PK (primary key) or a UNIQUE NOT NULL in it then every row has a match and they give the same answer.
Eg if every employee has a manager then in EMPLOYEE(e,...,m) non-NULL FOREIGN KEY (m) REFERENCES PK (e), so LEFT self-JOIN ON left.m = right.e gives the same result as INNER.
PS When you have a hypothesis you can look for counter-examples that might just happen to disprove it. Just about any small random-valued self-join would disprove yours. Did you try any? If you got a "feeling" about hypotheses about some special case(s) you could do that again.
