Considering two ways to declare a INNER JOIN , we have:
SELECT * FROM tabela1 INNER JOIN tabela2 ON tabela1.cd_tabela1 = tabela2.cd_tabela1;
and
SELECT * FROM tabela1 INNER JOIN tabela2 USING(cd_tabela1);
What's the difference between the two? In addition to the inner join , is there any difference if you consider other joins as the right outer join ?
Response :
ON is used when you have different or equal column names between tables, USING is already used when both tables share a column with the same name exact.
To present the differences between the JOINs I will show an example, using the following data tables below, whose field name is the same as the table:
A | B
---+---
1 | 3
2 | 4
3 | 5
4 | 6
INNER JOIN
It is an inner join using the equivalent queries resulting from the intersection of the two tables, that is, the two lines that the two tables have in common.
select * from A INNER JOIN B ON A.A = B.B;
A | B
--+--
3 | 3
4 | 4
LEFT OUTER JOIN
It is an outer join on the left that will result in all lines of A, in addition to all the common lines in B.
select * from A LEFT OUTER JOIN B ON A.A = B.B;
A | B
--+-----
1 | null
2 | null
3 | 3
4 | 4
RIGHT OUTER JOIN
It is an outer join to the right that will result in all lines of B, in addition to all common lines in A.
select * from A RIGHT OUTER JOIN B ON A.A = B.B;
A | B
-----+----
3 | 3
4 | 4
null | 5
null | 6
FULL OUTER JOIN
A complete outer join will give you the union of A and B, that is, all lines of one and all lines of B. If a line A does not have a match in B, the value of B will be 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 | 6
null | 5