• Now all data be ready for publish, We had made our blog alive now. Haha.



  • These days CCP GFW blocked all the IPs from US and when I switch my IP in google Cloud, the disk data cannot be restore again. I have to restart my blog totally, Now the website is still building…



What is the difference between “INNER JOIN” and “OUTER JOIN”?- Stack Overflow

Python PingBook 2 months ago (10-26) 19 0

Question

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Answer

Assuming you’re joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A B
– –
1 3
2 4
3 5
4 6

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

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they 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 left outer join will give all rows in A, plus any common rows in 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 right outer join will give all rows in B, plus any common rows in 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, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion 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 | 6
null | 5




Copyright from PingBook Blog, If not specified, they are original. This site uses BY-NC-SAProtocol authenticated.
For reprinting, please indicate the link of the original text:What is the difference between “INNER JOIN” and “OUTER JOIN”?- Stack Overflow
LIKE (0)
[1725641479@qq.com]
SHARE (0)
PingBook
Author:
We create, We sharing! Tag every value data your sharing
Submit comments
Cancel comments
emoji picture bold strikethrough center italic check in

Hi,you need to provide your name and email adress!

  • Name (Required)
  • Email (Required)
  • Website