0

I have a two tables, table X and table Y

Table X:

A B C
6 8 7
6 6 7
7 8 6

Table Y:

A B D 
5 8 7 
6 6 7
6 8 6

And when running the code 

SELECT * FROM X LEFT OUTER JOIN Y on X.B=Y.B;

It returns a table that has 5 rows.

How does this happen?

EDIT: I understand that this is the answer but I'd just like an explanation. Should have worded better :)

jophab
  • 5,356
  • 14
  • 41
  • 60
Brendan Fraser
  • 1
  • 2
  • Of course the result will return 5 rows. How many rows did you expect it to return? – Pham X. Bach Sep 08 '17 at 04:10
  • 1
    @BrendanFraser First row in X matches rows 1 and 3 in Y when comparing X.B = Y.B. Second row in X matches row 2 in Y. Third row in X matches rows 1 and 3. Total is 5 rows. This is expected. Do you think it should return a different count? – Kevin Suchlicki Sep 08 '17 at 04:10
  • Poor sample data to understand outer join. You need non-matching rows as well to get any difference! – jarlh Sep 08 '17 at 07:09

1 Answers1

0

First of all LEFT OUTER JOIN is equivalent to LEFT JOIN

As you know,

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

enter image description here

So in your case,

  • First row in X matches with 2 rows in Y

  • Second row in X matches with 1 row in Y

  • Third row in X matches with 2 rows in Y

Thus total 5 rows . Since there is no non matching rows in X , there won't be rows in results with null values.

More here

jophab
  • 5,356
  • 14
  • 41
  • 60