-2

If I have 2 tables, each table has one column only:
Table A:

col1
1
1
1

Table B:
col1
1
1
1

When I say:

select * from A left join B on a.col1 = b.col1

It has same output as :

select * from A,B (cartesian join).

Why is this?

Victor
  • 15,903
  • 67
  • 211
  • 390
  • 1
    Because `Left Join` is the same as an `Inner Join` but returns `Null` values for the right side if no match is present. The two tables are identical, so there's no reason it would return `Null` for any record. – Siyual Jun 10 '14 at 13:51
  • possible duplicate of [What is the difference between Left, Right, Outer and Inner Joins?](http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins) – AdamL Jun 10 '14 at 13:52
  • As long as the two tables contain the same values, the queries you posted will return the same data. Even `select * from A,B where A.col1=B.col1` will return the same data – Panagiotis Kanavos Jun 10 '14 at 13:54

1 Answers1

0

If you add another column for clarity's sake, I think the answer becomes easier to visualise:

Table A:
ID col1
1   1
2   1
3   1

Table B:
ID col1
1   1
2   1
3   1

So your cartesian product is:

A.ID    A.Col1  B.ID    B.ID
1       1       1       1
1       1       2       1
1       1       3       1
2       1       1       1
2       1       2       1
2       1       3       1
3       1       1       1
3       1       2       1
3       1       3       1

Now add the predicate WHERE A.Col1 = B.Col1 and you can see that this is true for all the rows. So a left join will return the same results as a cross join.

GarethD
  • 65,124
  • 9
  • 79
  • 119