Join commands (Join) in SQL

Question

I have some doubts about the commands inner join, left join, right join and full join, I don’t know if it’s just the join that makes the junctions between the tables, but along with it comes these commands, and I’m not able to understand their purpose, someone could explain to me how they work and when to use them?

Answer 1

For example, consider the diagram below:

CROSS JOIN

When we want to join two or more tables by crossing. That is, for each row of the FUNCIO table we want all POSITIONS or vice versa.

INNER JOIN

When we want to join two or more tables by coincidence. For each row of the FUNCINARIO table we want the corresponding CARGO that internally (INNER), in its attribute values, matches. In the case of FUNCTIONARY and POSITION the coincident internal attributes are codeCargo in the CARGO table and codeCargo in the FUNCIO table. To make effective the joining of the two tables it will be necessary to connect (ON) the two tables by their internal attributes (INNER) coincident.

LEFT OUTER JOIN

Observing the FUNCTIO table, suppose that the Tadeu employee does not have a position associated with it. If we wish to list all employees with their respective positions, including employees without posts, we could use all the power of the INNER JOIN joining by adding OUTER(EXTERNAL/OTHER) Employees who are not part of INNER JOIN, precisely to those without posts, as Tadeu. We can achieve this with the FUNCIO/CARGO junction through the FUNCIONARIO OUTER LEFT JOIN CARGO statement, which promotes the internal joining (INNER) of all employees to positions and lists still others (EXTERNAL/OUTER) not associated.

An important observation is that the link order (ON) makes no difference, ie: "ON (F.codCargo = C.codCargo)" is exactly equal to "ON (C.codCargo = F.codCargo)"

RIGHT OUTER JOIN

Observing the POSITION table suppose that the MANAGER position, with C3 code, is not referenced/associated by/to any employee in the FUNCTIO table. If we wish to list all POSITIONS and their respective EMPLOYEES, including POSITIONS WITHOUT EMPLOYEES, we could use the RIGTH OUTER JOIN.

OUTER FULL JOIN

Here we gather the power of the internal (JOIN) junctions (INNER), the listing of all other non-associated lines, both on the right side (RIGHT) of the junction and on the LEFT side (LEFT).

@reference

Answer 2

Dener, everyone joins tables with some differences:

When you use INNER JOIN with two tables for example,

table user and table position o, INNER Join creates a set of intersection as in mathematics, as in the image below, the INNER JOIN brings all related positions by ids 4 and 5 of the job table that contain in the user table and ignores the others, as in set A and B.

SELECT * FROM usuario A INNER JOIN cargo B on B.id = A.id_cargo

LEFT JOIN creates a set of relation between the two tables, however the following difference:

SELECT * FROM usuario A LEFT JOIN cargo  B A.id_cargo =  B.id

In the above query you will prefer the table specified in the user left, so you will have as a result users who are in charge and users who are not in charge and RIGHT JOIN does the opposite, ful JOIN makes the union between the tables.