Green Files

What is the difference between INNER JOIN and OUTER JOIN?

Day: 05/30/2022 - Time: 17:45:43

Inner join makes a join between two tables A and B where the projection will be all elements of A that are in B. Ex.: I want all the customers of a bank and its specific branches:

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

An outer join can be Left, Right and Center (or Cross).

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

Ex.: I want all the bank's customers and the amount of transactions carried out 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 used the left join so that all customers are printed, even if they have not moved.

The Right join is equivalent to the left join, but with the order reversed.

The Center (Or Cross) join crosses the data of A and B, that is, it prints all the elements of A and those of B, regardless of whether one is linked to the other.

Ex.: I want to print a list of Chemical Analysis Results of a certain element and the date of analysis.

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

The query above will print all the analysis dates together with the chemical analysis result if there is any analysis associated with the date in question and All analyzes together with the analysis date in question. It will also print all the dates in which no analysis took place and all the elements whose analysis was not carried out.