A brief explanation of Inner, Left, Right, Outer / Full and Cross Join

Let's take these two tables as the basis:

Usingbothinallexamples,let'sillustratethemostcommontypesofjoin.

InnerJoin

This is a common join format, which returns data only when the two tables have matching keys in the ON clause of the join.

Query:

SELECT TabelaA.*, TabelaB.* FROM TabelaA INNER JOIN TabelaB ON TabelaA.Chave = TabelaB.Chave

Result:

LeftJoin

It is one of the most used forms of join , which returns Table A and only records that match in> join in TableB (or null fields for unmatched fields).

Query:

SELECT TabelaA.*, TabelaB.* FROM TabelaA LEFT JOIN TabelaB ON TabelaA.Chave = TabelaB.Chave

Result:

RightJoin

It follows the same reasoning as Left Join , but applying to table B instead of A:

Query:

SELECT TabelaA.*, TabelaB.* FROM TabelaA RIGHT JOIN TabelaB ON TabelaA.Chave = TabelaB.Chave

Result:

FullOuterJoin

Known as OUTER JOIN or simply FULL JOIN , this returns all records of both tables .

Query:

SELECT TabelaA.*, TabelaB.* FROM TabelaA FULL OUTER JOIN TabelaB ON TabelaA.Chave = TabelaB.Chave

Result:

CrossJoin

Basically it is the Cartesian product between the two tables. For each row of TableA, all the Rows of TableB are returned.

It is easier to understand Cross Join as a "Join without clause ON", that is, all combinations of lines of A and B are returned.

In fact, if you do a Cross Join with ON clause, it "turns" a mere Inner Join.

Query:

SELECT TabelaA.*, TabelaB.* FROM TabelaA CROSS JOIN TabelaB 

Or:

SELECT TabelaA.*, TabelaB.* FROM TabelaA, TabelaB 

Result:

Considerations

Note that all of the requested fields in select always return (as long as they exist in the table, of course), regardless of whether they exist for that particular row.

What happens if a line is returned to just one of the tables is that the fields of the other come with null content.

Usually, if you need to differentiate a null that actually exists in the table from a null by mismatch, just see if the fields used in the condition of ON do not return null as well. p>     

I like images to understand joins.

Inner join makes a join between two tables A and B where the projection will be all elements of A that are in B.

Eg: I want all clients of a bank and its agencies:

select * from Clientes inner join Agencias on Cliente.idAgencia = Agencias.idAgencia

An outer join can be Left , Rigth and Center .

A left join makes a junction between A and B where the projection will be all elements of A, whether or not they are in B.

Eg: I want all the bank's customers and the number of transactions made in April 2013:

select nomeCliente, count(idMovimentacao) from Clientes left outer join Movimentacoes on Clientes.idCliente = Movimentacoes.idCliente where Movimentacao.dtCompetencia = '04/2013'

In the above query we use left join so all clients will be printed, even if they have not made any moves.

The Rigth join is equivalent to the left join, but with the command changed.

The Center (or Cross) join intersects the data of A and B, that is, it prints all the elements of A and those of B, regardless of one being connected to the other.

Example: I want to print a list of Chemical Analysis Results for a given element and the date of the analysis.

select * from ResultadosAnalisesQuimicasElementos center join DatasAnalises on AnaliseQuimica.idAnalise = DatasAnalises.idAnalise

The above query will print all test dates together with the result of the chemical analysis if there is any analysis associated with the date in question and All analyzes together with the date of the analysis in question. All dates on which no analysis has been performed and all elements that have not been analyzed will also be printed.

Below is a link with more examples on DevMedia

This page is something to be printed and pasted into the bay: link

Basically, INNER JOIN Orders clients only return customers who have placed orders.

Customers OUTER JOIN Orders return customers without requests (Orders columns will be NULL) and, if referential integrity is not enforced, requests without clients. Not very useful.

More useful is LEFT JOIN Customers (also known as LEFT OUTER JOIN) where only customers without orders can appear (but not unmerited requests from customers).