CODEWITHSUNDEEP
sqlsql-serverdatabaseouter-join
Drux
Drux

Reputation: 12660

How can A left outer join B return more rows than are in A?

What is it wrong about this simple SQL outer join?

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

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

Upvotes: 7

Views: 6194

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

There is nothing strange about it (and this situation applies to inner joins too). A left outer join:

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

So at minimum the query will return 25766 rows but there could be more. It is possible to have one row in table A that matches many rows in table B. Example:

A             B             Result
+----+----+  +-----+----+   +------+-----+-----+------+
|  id|   b|  |   id|   b|   |  a.id|  a.b|  b.b|  b.id|
+----+----+  +-----+----+   +------+-----+-----+------+
|   1|  10|  |  123|  10|   |     1|   10|   10|   123|
+----+----+  |  456|  10|   |     1|   10|   10|   456|
             +-----+----+   +------+-----+-----+------+

This returns two rows even though there is one row in table A.

Upvotes: 3

user743382
user743382

Reputation:

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

  A      B
+---+  +---+---+
| b |  | b | c |
+---+  +---+---+
| 1 |  | 2 | 1 |
| 2 |  | 2 | 2 |
+---+  +---+---+

When you left-join from A to B on column b, you get

+-----+------+------+
| A.b | B.b  | B.c  |
+-----+------+------+
|   1 | NULL | NULL |
|   2 |    2 |    1 |
|   2 |    2 |    2 |
+-----+------+------+

which gives three rows in total, even though both A and B only have two rows each.

Upvotes: 16

Related Questions