What is the difference between an inner and an outer join?
What is the difference between a inner and a outer join?
And what is the function of modifiers?
leftrightfull
2 answers
Assuming that a join of columns without duplicates is being made, which is a common case:
A inner join of A and B will deliver the result of the intersection of Sets A and B. In other words, the Part inner –intersection– in a Venn diagram.
A full outer join between A and B will deliver the result of the Union of A and B. In other words, the Part outer - Union-in a diagram of Venn .
Examples:
Suppose we have two tables, with a single column each and the following data:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are only found in A, (3,4) are common and (5,6) are only found in B.
Inner Join
An inner join– using any of the equivalent query syntax-gives you the intersection of both tables, that is, the rows that both tables have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a, b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A outer join on the left, will give you all the rows of A, including the common rows between A and B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
A outer join on the right will give you all the rows of B, including the common rows with A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the Union of A and B; that is, all rows of A and all rows of B. If a row in A does not have a corresponding row in B, the portion of B is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 5
null | 6
Venn diagrams
This same, we can see with Venn diagrams:
Image SQL Joins.svg by Arbeck, shared under license CC BY 3.0.
I'm going to use the same jachguate example which is very clear by adding a few small details.
Glossary
- inner join
- outer join
-- Crear tabla A (tabla Izquierda)
CREATE TABLE A
(
a INT
);
-- Crear tabla B (tabla derecha)
CREATE TABLE B
(
b INT
);
-- Insertar datos
Insert into A (a) Values (1);
Insert into A (a) Values (2);
Insert into A (a) Values (3);
Insert into A (a) Values (4);
Insert into B (b) Values (3);
Insert into B (b) Values (4);
Insert into B (b) Values (5);
Insert into B (b) Values (6);
COMMIT;
-- Tabla A
SELECT * FROM A;
-- Tabla B
SELECT * FROM B;
/* Inner Join. */
-- Unión interna, filas que ambas tablas tienen en común.
select * from A INNER JOIN B on A.a = B.b;
select A.*, B.* from A, B where A.a = B.b;
/* Left outer join */
-- Unión externa por la izquierda, todas las filas de A (tabla izquierda) relacionadas con B, así estas tengan o no coincidencias.
select * from A LEFT OUTER JOIN B on A.a = B.b;
select A.*,B.* from A,B where A.a = B.b(+);
/* Right outer join */
-- Unión externa por la derecha, todas las filas de B (tabla derecha), así estas tengan o no coincidencias con A.
select * from A RIGHT OUTER JOIN B on A.a = B.b;
select A.*,B.* from A,B where A.a(+) = B.b;
/* Full outer join */
-- Unión externa completa, unión externa por la izquierda unida a unión externa por la derecha.
-- En oracle:
select * from A FULL OUTER JOIN B on A.a = B.b;
-- En MySql no está implementado FULL OUTER JOIN, para obtener este mismo resultado:
select * from A LEFT OUTER JOIN B on A.a = B.b
UNION
select * from A RIGHT OUTER JOIN B on A.a = B.b;
See: