I'm wondering how you can differentiate each one of these different joins ...
Simple Example: Allows if you have a Students table, along with a Lockers table.
Each student could be designated to some locker, so there's a "LockerNumber" column within the student table. Several student may potentially be in one locker, but especially noisy . the college year, you might have some incoming students without lockers plus some lockers which have no students designated.
With regard to this situation, allows if you have 100 students, 70 which have lockers. You've got a total of 50 lockers, 40 which have a minimum of 1 student.
is the same as "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows
LEFT OUTER JOIN could be "show me all students, using their corresponding locker should they have one".
This can be an over-all student list, or could be employed to identify students without any locker.
Returns 100 rows
RIGHT OUTER JOIN could be "show me all lockers, and also the students designated for them if you will find any".
This may be accustomed to identify lockers which have no students designated, or lockers which have a lot of students.
Returns 80 rows (listing of 70 students within the 40 lockers, as well as the 10 lockers without any student)
FULL OUTER JOIN could be silly and most likely very little use.
Something similar to "show me all students and all sorts of lockers, and match them up where one can"
Returns 110 rows (all 100 students, including individuals without lockers. As well as the 10 lockers without any student)
Mix JOIN can also be fairly silly within this scenario.
It does not make use of the linked "lockernumber" area within the students table, which means you essentially finish track of a large giant listing of every possible student-to-locker pairing, whether it really is available.
Returns 5000 rows (100 students x 50 lockers). Might be helpful (with blocking) like a beginning point to match the brand new students using the empty lockers.
from Coding Horror A Visible Explanation of SQL Joins