What is the difference between INNER JOIN and OUTER JOIN?

Asked

Viewed 233,186 times

328

What’s the difference between INNER JOIN and OUTER JOIN? Can you give me some examples?

4 answers

445


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


Let’s base these two tables:

TabelaA e TabelaB

Using both in all examples, let’s illustrate the most common types of Join.

 

Inner Join

Gráfico Inner Join

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

Query:

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

Upshot:

Inner Join

 

Left Join

Gráfico Left Join

It is one of the most used formats of Join, who returns to Table The whole and only the records that match with the equality of Join in Tabelab (or null fields for unmatched fields).

Query:

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

Upshot:

Left Join

 

Right Join

Gráfico Right Join

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

Query:

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

Upshot:

Right Join

 

Full Outer Join

Gráfico Full Outer Join

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

Upshot:

Full/Outer Join

 

Cross Join

Gráfico Cross Join

Basically it is the Cartesian product between the two tables. For each row of Tabelaa, all lines of Tabelab are returned.

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

Even, if you do a Cross Join with ON, it "turns" into a mere Inner Join.

Query:

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

Or else:

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

Upshot:

Cross Join

 

Considerations

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

What happens if one row is returned to only one of the tables is that the fields of the other come with content null.

Usually, if you need to differentiate a null that actually exists in the table from a null by missing match, just see if the fields used in the ON did not return null also.

  • 2

    I have researched this also so I have saved, http://stackoverflow.com/questions/406294/left-join-left-left-join-outer-in-sql-server, Note that as stated in the first comment, from the correct answer LEFT JOIN and LEFT OUTER JOIN are the same thing.

  • 4

    Not happy to have gotten the first gold medal "Great answer" from the site, you also just took the second with this answer. Congratulations! There’s only two of those on the site so far and they’re both yours!

  • 2

    @Grateful Victorstafusa. This one is cooler to see it arrive in the 100, pq is own development, the other was more the work of translation and adaptation. But there are other site contributors coming in at 100 Ajá by what I’ve been seeing :)

  • 2

    some images were removed, could edit and replace the images?

  • 3

    @V.Avancini the images taken were previous versions of the current ones, less complete. Nothing was lost, I just improved the diagramming and put important details in relation to null (both previous and current I produced). Follow the current ones that the representation is much better.

127

I like images to understand joins.

TSQL join type

  • 1

    has some example of anti semi Join with 3 tables?

49

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

Ex.: I want all the clients of a bank and its certain agencies:

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

An Outer Join may be Left, Rigth and Center (or Cross).

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

Ex.: I want all bank 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 query above we use left Join so that all customers are printed, even if they have not made any movement.

Rigth Join is equivalent to left Join, but with order switched.

The Center (Or Cross) Join crosses the data of A and B, ie prints all elements of A and B, regardless of whether one is connected to the other.

Ex.: I want to print a Chemical Analysis Report of a particular element and the date of the analysis.

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

The above query will print all analysis dates together with the result of the chemical analysis if there is any analysis associated with the date in question and All analyses together with the date of the analysis in question. Also printed are all dates when no analysis has taken place and all elements whose no analysis has been made.

Below is a link with more examples on Devmedia

  • 1

    And which JOIN would I not select the tuples of B, regardless of whether or not they are connected?

  • 2

    @Marconi WHAT?!

45

This page is something to be printed and pasted in the stall: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Basically, Customers INNER JOIN Orders returns only customers who have placed orders.

Customers OUTER JOIN Orders returns customers without orders (the Orders columns will be with NULL) and, if referential integrity is not imposed, orders without customers. Not very useful.

Most useful is Customers LEFT JOIN Orders (also known as LEFT OUTER JOIN) where only customers without orders can appear (but not customers' unmatched orders).

Browser other questions tagged

You are not signed in. Login or sign up in order to post.