I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.
Assume these two tables:
SELECT * FROM faculty;
ID | FIRST_NAME | LAST_NAME |
---|---|---|
1 | Jon | Emmons |
2 | Zach | Tirrell |
3 | Evelyn | Stiller |
SELECT * FROM class;
CLASS_ID | CATALOG_NUMBER | INSTRUCTOR_ID |
---|---|---|
1 | CS3600 | 1 |
2 | CS3020 | 2 |
3 | CS2000 | |
4 | CS1100 | 1 |
Inner Join
The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.
SELECT *
FROM faculty, class
WHERE faculty.id = class.instructor_id;
ID | FIRST_NAME | LAST_NAME | CLASS_ID | CATALOG_NUMBER | INSTRUCTOR_ID |
---|---|---|---|---|---|
1 | Jon | Emmons | 1 | CS3600 | 1 |
2 | Zach | Tirrell | 2 | CS3020 | 2 |
1 | Jon | Emmons | 4 | CS1100 | 1 |
Here we see the three rows where faculty.id had a match to class.instructor_id.
Left Join
A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.
SELECT *
FROM faculty LEFT JOIN class
ON faculty.id=class.instructor_id;
ID | FIRST_NAME | LAST_NAME | CLASS_ID | CATALOG_NUMBER | INSTRUCTOR_ID |
---|---|---|---|---|---|
1 | Jon | Emmons | 1 | CS3600 | 1 |
1 | Jon | Emmons | 4 | CS1100 | 1 |
2 | Zach | Tirrell | 2 | CS3020 | 2 |
3 | Evelyn | Stiller |
Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.
Right Join
Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.
SELECT *
FROM faculty RIGHT JOIN class
ON faculty.id=class.instructor_id;
ID | FIRST_NAME | LAST_NAME | CLASS_ID | CATALOG_NUMBER | INSTRUCTOR_ID |
---|---|---|---|---|---|
1 | Jon | Emmons | 1 | CS3600 | 1 |
2 | Zach | Tirrell | 2 | CS3020 | 2 |
3 | CS2000 | ||||
1 | Jon | Emmons | 4 | CS1100 | 1 |
Now we see rows from the class table which do not have a faculty counterpart.
Outer Join
The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.
SELECT *
FROM faculty FULL OUTER JOIN class
ON faculty.id = class.instructor_id;
ID | FIRST_NAME | LAST_NAME | CLASS_ID | CATALOG_NUMBER | INSTRUCTOR_ID |
---|---|---|---|---|---|
1 | Jon | Emmons | 1 | CS3600 | 1 |
1 | Jon | Emmons | 4 | CS1100 | 1 |
2 | Zach | Tirrell | 2 | CS3020 | 2 |
3 | Evelyn | Stiller | |||
3 | CS2000 |
Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.
For the sake of consistency, I would have written your inner join as:
SELECT *
FROM faculty
INNER JOIN class ON
faculty.id = class.instructor_id;
This way, the join is more explicit.
Concise and clear illustration in such a simple format. I read straight through and the all the join ideas are cleared to me all of a sudden. CONGRADULATIONS!
Thanks Alan! Glad this was helpful!