Difference between RIGHT JOIN and LEFT JOIN

Question

I have a little knowledge in relationship tables, read the answers to the question about What is the difference between INNER JOIN and OUTER JOIN?, and understood, theoretically.

LEFT JOIN
Returns all records from the left table (table A) and any matches with the right table (table B).

RIGHT JOIN
Return all records from the right table (table B) and any matches with the left table (table A).

Using the example below:

Exemplo de LEFT JOIN

SELECT *
  FROM tabela_a a
  LEFT JOIN tabela_b b
  ON b.nome = a.nome;

Exemplo de RIGHT JOIN

SELECT *
  FROM tabela_b b
  RIGHT JOIN tabela_a a
  ON a.nome = b.nome;

Both return:

|   nome |
|--------|
|  Maria |
|    Bia |
| (null) |
| (null) |
| (null) |
| (null) |
| (null) |

I made an example at Sqlfindle to better understand.

---

Question:

Theoretically, there is the difference, I would like to understand for which the difference, and the reason for having the two terms, of how the two were created, and created by someone.

Thank you.

Answer 1

Own experience:

Depending on how you diagram your bd, may have n tables, and depending on the need of your query, you may need to use a table as the main of your query, exemplifying with a diagramming without much embellishment:

In the example I have 6 tables, a tabela 3 clearly is the main of my system, it which makes connection with all the others, could be a table of plans, for example...

If by chance I want to create just one query bringing information concerning the id of tabela 3, and other additional information from other tables, for example I may want to bring all information regarding tabela 4, but which has no connection with the tabela 2 and 6, then I could use both the resources of the left join, how much of right join so that both could meet my need.

In mathematical terms, remembering that little class of sets, it might be easier to visualize:

In this example, I need to get information that is primarily on tabela 1 and not in the tabela 2, and are primarily in the tabela 5 but not in table 6.

There are two ways of doing such a query, one using sub-querys (The most recommended when there are conditions in the query of the "sub-domain", which do not depend only on the relationship of the tables), or other that is using the resource of the left join and right join to bring only the data that is needed from one or another table, in which case the select would look something like this [edit]

Answer 2

In terms of functioning there is no difference only in terms of concept as the name says righ or left or left if you use

SELECT *
  FROM tabela_a a
  LEFT JOIN tabela_b b
  ON b.nome = a.nome;

you will bring all the results of the table that is left this tabela_a EVEN those that do not match the table on the right. In the case if there is no comparison it brings null.

If you trade it for the right it would bring all the results of tabela_b (to the right of table A).