What’s the difference between "ON" and "USING"?

Question

Considering two ways to declare one 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 is the difference between the two? Besides the Inner Join, there is some difference if it is considered other joins like the right Outer Join?

Answer 1

Answer:

The ON is used when there are different or equal column names between the tables, USING is using when both tables share a column with the same exact name.

To present the differences between JOIN I will show an example, using the following data tables below, whose field name is equal to that of the table:

A  |  B
---+---
1  |  3
2  |  4
3  |  5
4  |  6

INNER JOIN

It is an internal junction using the equivalent queries resulting from the intersection of the two tables, i.e., 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 external junction to the left that will result in all lines of A, plus all 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 external junction on the right that will result in all lines of B, plus all common lines on 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

It is a complete external junction will give you the union of A and B, that is, all lines of one and all lines of B. If a line A has no correspondence 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