How does the left outer join of B return more rows than in A?

Question

What is wrong with this simple external SQL join?

select count(*) from A -- 25766 select count(*) from B -- 1242 select count(*) from A left outer join B on Ab = Bb -- 310176 

25766, 1242, and 310176 rows are returned, respectively. (This is for Microsoft SQL Server 2012.) How does A left outer join B ever return more rows than exists in A , especially considering this Venn diagram? I think I'm making a stupid mistake, but what is it?

Answer 1

This can happen if column b not unique in table b . Suppose you have this data:

  Ab
 + --- + + --- + --- +
 |  b |  |  b |  c |
 + --- + + --- + --- +
 |  1 |  |  2 |  1 |
 |  2 |  |  2 |  2 |
 + --- + + --- + --- +

When you leave connection A to b in column b , you will get

  + ----- + ------ + ------ +
 |  Ab |  Bb |  Bc |
 + ----- + ------ + ------ +
 |  1 |  NULL |  NULL |
 |  2 |  2 |  1 |
 |  2 |  2 |  2 |
 + ----- + ------ + ------ +

which gives three lines in total, although both A and b have only two lines.

Answer 2

There is nothing strange in this (and this situation applies to internal associations). Left outer join:

• Returns all rows from A cross B where the join condition matches
• And returns all rows from A, where the join condition does not match

So, at a minimum, the query will return 25,766 rows, but there may be more. It is possible that table A has one row that corresponds to many rows in table B. Example:

 AB Result +----+----+ +-----+----+ +------+-----+-----+------+ | id| b| | id| b| | a.id| ab| bb| b.id| +----+----+ +-----+----+ +------+-----+-----+------+ | 1| 10| | 123| 10| | 1| 10| 10| 123| +----+----+ | 456| 10| | 1| 10| 10| 456| +-----+----+ +------+-----+-----+------+ 

This returns two rows, although table A has one row.