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
```

I believe an `UPDATE FROM`

with a `JOIN`

will help:

## MS SQL

```
UPDATE
Sales_Import
SET
Sales_Import.AccountNumber = RAN.AccountNumber
FROM
Sales_Import SI
INNER JOIN
RetrieveAccountNumber RAN
ON
SI.LeadID = RAN.LeadID;
```

## MySQL and MariaDB

```
UPDATE
Sales_Import SI,
RetrieveAccountNumber RAN
SET
SI.AccountNumber = RAN.AccountNumber
WHERE
SI.LeadID = RAN.LeadID;
```

## Best Solution

This is an example of the

`greatest-n-per-group`

problem that has appeared regularly on StackOverflow.Here's how I usually recommend solving it:

Explanation: given a row

`p1`

, there should be no row`p2`

with the same customer and a later date (or in the case of ties, a later`id`

). When we find that to be true, then`p1`

is the most recent purchase for that customer.Regarding indexes, I'd create a compound index in

`purchase`

over the columns (`customer_id`

,`date`

,`id`

). That may allow the outer join to be done using a covering index. Be sure to test on your platform, because optimization is implementation-dependent. Use the features of your RDBMS to analyze the optimization plan. E.g.`EXPLAIN`

on MySQL.Some people use subqueries instead of the solution I show above, but I find my solution makes it easier to resolve ties.