What is the difference between INNER JOIN
and OUTER JOIN
? Can you give me some examples?
What is the difference between INNER JOIN
and OUTER JOIN
? Can you give me some examples?
Let's take these two tables as the basis:
Usingbothinallexamples,let'sillustratethemostcommontypesofjoin.
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:
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:
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:
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:
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:
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>
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).