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:
Using both in all examples, let’s illustrate the most common types of Join.
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:
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:
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:
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:
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:
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.
127
I like images to understand joins.
-
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 sql join
You are not signed in. Login or sign up in order to post.
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 JOINandLEFT OUTER JOINare the same thing.– Guilherme Lautert
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!
– Victor Stafusa
@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 :)
– Bacco
some images were removed, could edit and replace the images?
– V.Avancini
@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.
– Bacco