• What is the difference between INNER JOIN and OUTER JOIN ?

  • What do LEFT JOIN , RIGHT JOIN and FULL JOIN mean?

The translation of the question " Difference between INNER and OUTER joins " @cdv .

2 answers 2

You can also use this scheme to understand the joining of tables:

SQL Join

Translation of the answer “Difference between INNER and OUTER joins” @Teoman Shipahi

  • 3
    Um ... I don’t understand something, or what is the genius of the idea to join, and then throw out all the data from another table? - Qwertiy ♦
  • one
    @Qwertiy cleared the intersection of the tables, then everything is clearly visible. - Denis
  • one
    @Qwertiy in the context of a question-answer site like SO, so, say, you can look for unanswered questions : connect questions with answers and filter only those that have no matches with the answers. Cases are not so rare. - D-side
  • 3
    It is not even a translation. - Nakilon
  • one
    @Qwertiy If we consider the option with the left join, then such a query is needed when it is necessary to find in table A records in which the key field is somehow filled (not null) but there is no corresponding entry in table B. - ApInvent

Suppose you want to join in columns without duplicates, which is quite common:

  • Internal junction A and B: A intersects B, i.e. inner part of the Venn diagram intersection.

  • External connection A and B: A connects to B, i.e. the outer part of the compound in the Venn diagram.

Examples

Suppose you have two tables. Each consists of one column, with the following values:

 AB - - 1 3 2 4 3 5 4 6

Please note that (1,2) are unique to A, (3,4) are common elements, and (5,6) are unique to B.

Internal connection

An inner join using one of the equivalent queries results in the intersection of two tables, that is, two rows common to each of them.

 select * from a INNER JOIN b on aa = bb; select a.*, b.* from a, b where aa = bb; a | b --+-- 3 | 3 4 | 4

Left outer join

The result of the left outer join is all rows of table A plus all rows of table B matching the rows of table A.

 select * from a LEFT OUTER JOIN b on aa = bb; select a.*, b.* from a, b where aa = bb(+); a | b --+----- 1 | null 2 | null 3 | 3 4 | 4

Right outer join

The result of the right outer join is all the rows in table B plus all the rows in table A that match the rows in table B.

 select * from a RIGHT OUTER JOIN b on aa = bb; select a.*,b.* from a,b where aa(+) = bb; a | b -----+---- 3 | 3 4 | 4 null | 5 null | 6

Full external connection

The result of a full outer join is the join of tables A and B, i.e. all rows A and all rows B. If any element of table A has no match in table B, this part B is empty and vice versa.

 select * from a FULL OUTER JOIN b on aa = bb; a | b -----+----- 1 | null 2 | null 3 | 3 4 | 4 null | 6 null | 5

Translation of the answer “ Difference between INNER and OUTER joins ” @Mark Harrison .

  • Is the LEFT OUTER JOIN the same as the LEFT JOIN ? According to the examples it is. But is it really not reflected in the answer. - jekaby
  • one
    @jekaby yes it is. Left and right joins are external (see outer join ). - D-side