In my experience, RIGHT OUTER JOINs are less common, and should probably be avoided. Unless of course there is good reasoning.
When referring to an OUTER JOIN, I am talking about:
- LEFT JOIN
- LEFT OUTER JOIN (same as above)
- RIGHT JOIN
- RIGHT OUTER JOIN (same as above)
When referring to an INNER JOIN, I am talking about the one and only INNER JOIN.
Example Data
MariaDB [companies]> SELECT * FROM companies;
+----+---------------+
| id | name |
+----+---------------+
| 1 | ACME Inc. |
| 2 | QA Company |
| 3 | Motodroid LLC |
+----+---------------+
3 rows in set (0.00 sec)MariaDB [companies]> SELECT * FROM users;
+----+-----------+-----------+-----------+----------------------------+
| id | companyId | firstName | lastName | email |
+----+-----------+-----------+-----------+----------------------------+
| 1 | 1 | Billy | McGriddle | billy.mcgriddle@acme.inc |
| 2 | 1 | John | Doe | john.doe@acme.inc |
| 3 | 2 | Jane | Smith | jane.smith@example.org |
| 4 | NULL | Bill | Nye | bill.nye@thescienceguy.com |
| 5 | NULL | Frank | Jones | frank.jones@somewhere.com |
+----+-----------+-----------+-----------+----------------------------+
5 rows in set (0.00 sec)Left Join
When performing a LEFT JOIN between two tables, users and companies, you will select all the records from users and any matching records from companies. If there are no matching records in companies, then the returned values will be NULLed out.
MariaDB [companies]> SELECT u.*, c.* FROM users u LEFT JOIN companies c ON u.companyId = c.id;
+----+-----------+-----------+-----------+----------------------------+------+------------+
| id | companyId | firstName | lastName | email | id | name |
+----+-----------+-----------+-----------+----------------------------+------+------------+
| 1 | 1 | Billy | McGriddle | billy.mcgriddle@acme.inc | 1 | ACME Inc. |
| 2 | 1 | John | Doe | john.doe@acme.inc | 1 | ACME Inc. |
| 3 | 2 | Jane | Smith | jane.smith@example.org | 2 | QA Company |
| 4 | NULL | Bill | Nye | bill.nye@thescienceguy.com | NULL | NULL |
| 5 | NULL | Frank | Jones | frank.jones@somewhere.com | NULL | NULL |
+----+-----------+-----------+-----------+----------------------------+------+------------+
5 rows in set (0.00 sec)Above you see that all users are returned, but the last two users have NULLed out company information. That is because these users do not have a matching record in the companies table.
Right Join
When performing a RIGHT JOIN between two tables, users and companies, you will select all the records from companies and any matching records from users. If there are no matching records in users, then the returned values will be NULLed out.
MariaDB [companies]> SELECT u.*, c.* FROM users u RIGHT OUTER JOIN companies c ON u.companyId = c.id;
+------+-----------+-----------+-----------+--------------------------+----+---------------+
| id | companyId | firstName | lastName | email | id | name |
+------+-----------+-----------+-----------+--------------------------+----+---------------+
| 1 | 1 | Billy | McGriddle | billy.mcgriddle@acme.inc | 1 | ACME Inc. |
| 2 | 1 | John | Doe | john.doe@acme.inc | 1 | ACME Inc. |
| 3 | 2 | Jane | Smith | jane.smith@example.org | 2 | QA Company |
| NULL | NULL | NULL | NULL | NULL | 3 | Motodroid LLC |
+------+-----------+-----------+-----------+--------------------------+----+---------------+
4 rows in set (0.00 sec)Above you see that all the companies are returned. However, only three users are returned. The behavior, to me, seems less natural. When performing SELECT * FROM users […], I would not expect to have any missing user values. But using RIGHT JOIN allows that.
Inner Join
When performing an INNER JOIN between two tables, users and companies, you will select all the records from users and only matching records from companies.
MariaDB [companies]> SELECT u.*, c.* FROM users u INNER JOIN companies c ON u.companyId = c.id;
+----+-----------+-----------+-----------+--------------------------+----+------------+
| id | companyId | firstName | lastName | email | id | name |
+----+-----------+-----------+-----------+--------------------------+----+------------+
| 1 | 1 | Billy | McGriddle | billy.mcgriddle@acme.inc | 1 | ACME Inc. |
| 2 | 1 | John | Doe | john.doe@acme.inc | 1 | ACME Inc. |
| 3 | 2 | Jane | Smith | jane.smith@example.org | 2 | QA Company |
+----+-----------+-----------+-----------+--------------------------+----+------------+
3 rows in set (0.00 sec)Above, you see that only users that belong to a company are returned.
In Summary
In summary, inner joins will return only records that have a matching join in both tables. Outer joins will return all records from one table, and also records from the other table. If there is no matching join, then the values will be NULLed out for that particular row.