The confusing difference beweteen different JOINs in SQL

There are two kinds of JOINs: one is INNER JOIN, the other is OUTER JOIN.

Sounds like similar, but very different:

INNER JOIN means the result of a query should exist in the source tables.
OUTER JOIN means the result of a query CAN be some NULLs partially.

INNER JOIN is a keyword of SQL, but OUTER JOIN not.
OUTER JOIN includes two types JOINs: one is LEFT JOIN, the other is RIGHT JOIN,

NOTICE: LEFT JOIN & RIGHT JOIN are keywords of SQL.

Now let's look at some examples to understand things more clearly.

Given two tables:

TABLE 1: exam

ID Score
------------------------
1 A-
2 B+
5 C-

TABLE 2: student

ID Name
------------------------
1 Joe
5 Geogre
7 Ben

First SQL query:
SELECT e.ID, s.Name, e.Score FROM exam AS e INNER JOIN student AS s ON s.ID=e.ID;

So we get:

ID Name Score
-----------------------------
1 Joe A-
5 Geogre C-

In fact, this query is equaled to:
SELECT e.ID, s.Name, e.Score FROM exam AS e, student AS s WHERE s.ID=e.ID;

Second SQL query:
SELECT e.ID, s.Name, e.Score FROM exam AS e LEFT JOIN student AS s ON s.ID=e.ID;

We should get the following this time:
ID Name Score
-----------------------------
1 Joe A-
2 NULL B+
5 Geogre C-

Third SQL query:
SELECT e.ID, s.Name, e.Score FROM exam AS e RIGHT JOIN student AS s ON s.ID=e.ID;

We should get the following this time:
ID Name Score
-----------------------------
1 Joe A-
5 Geogre C-
7 Ben NULL

So you find the difference!

LEFT JOIN will try to find every match for the table BEFORE the statement LEFT JOIN, when nothing matched can be found, then a NULL will be used.

RIGHT JOIN will try to find every match for the table AFTER the statement RIGHT JOIN, when nothing matched can be found, then a NULL will be used.