Assuming you're joining on columns with no duplicates, which is by far the most common case:

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

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.

A left outer join will give all rows in A, plus any common rows in B.

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.

- 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 32 43 54 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 | 34 | 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 | null2 | null3 | 34 | 4
```

**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 | 4null | 6null | 5
```

## No comments:

## Post a Comment