What is the difference between "INNER JOIN" and "OUTER JOIN"?

Question

Also how do `LEFT JOIN`, `RIGHT JOIN` and `FULL JOIN` fit in?

Answer 1

In its simplest form, a join generates a 'result set' (looks like a table) combining columns from 2 tables based on some specified join criteria - where a reference field in the "left" table matches a field value in the "right' table.

So with that in mind:

INNER JOIN: result set will only reflect records that satisfy the join criteria

LEFT OUTER JOIN: result set will reflect all records in the left table, of course, including those that satisfy the join criteria

RIGHT OUTER JOIN: result set will reflect all records in the right table, of course, including those that satisfy the join criteria

FULL OUTER JOIN: result set will reflect all records in both tables, regardless of whether they satisfy the join criteria

Let's further explain with a clear and simple example.

Imagine that we have a STUDENT table and a MAJOR table ... STUDENT contains a basic student identifying columns plus a foreign key called MAJOR_ID (which refers to the ID column in the MAJOR table. As we said, the MAJOR table contains an ID, and a name field to be assigned the matriculated field of study like Computer Science, History, or Mathematics ...

Not all students are associated to a Major, and not all Majors have students assigned (at least in this notional example).

As defined above:

1) An INNER JOIN would return all students that were matriculated (assigned to a major)

SELECT * FROM STUDENT S INNER JOIN MAJOR M ON S.MAJOR_ID = M.ID;

2) A LEFT OUTER JOIN would return all students, matriculated or not.

SELECT * FROM STUDENT S LEFT OUTER JOIN MAJOR M ON S.MAJOR_ID = M.ID;

3) A RIGHT OUTER JOIN would return all majors, regardless of whether students are yet assigned to them

SELECT * FROM STUDENT S RIGHT OUTER JOIN MAJOR M ON S.MAJOR_ID = M.ID;

4) A FULL OUTER JOIN would return all Students and all Majors

SELECT * FROM STUDENT S FULL OUTER JOIN MAJOR M ON S.MAJOR_ID = M.ID;

FYI: Not all SQL engines support the FULL OUTER JOIN Syntax ...

Hope this sheds some light on the topic ...