1

I have two tables test1 and test2 both with single column containing some values.

I have applied the inner and outer joins but have confusion with the output.

Create table test1
( id int)
insert into test1 values (1)
insert into test1 values (1)
insert into test1 values (1)


Create table test2
( id int)
insert into test2 values (1)
insert into test2 values (1)
insert into test2 values (NULL)

select a.id from test1 a inner join test2 b on a.id = b.id

I was expecting,

1
1
Null

as output for inner join, left join and right join.

But the original output was,

1
1
1
1
1
1

Could you please help me in understanding this on all the joins.

bob
  • 47
  • 6

1 Answers1

1

Each one of the three 1s in test1 joined with each one of the two 1s in test2, and this yielded the 3x2=6 rows you got in the result-set. There is nothing different between the first,second and third 1 in test1 and nothing different between the first and second 1 in test2.

Also, keep in mind that all of the following conditions:

NULL = 1
NULL <> 1
NULL = NULL
NULL <> NULL

are false. All conditions which have a NULL at one side will evaluate to false. This is because a NULL represents an unknown value.

What you expected is quite wrong, as you can see. It seems you expected the first row of test1 to be joined with the first row of test2 and so on. There is no such "magic" in sql - the entire logic of the join is placed within the ON clause, which joined the 1s as expained before.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • You understood my doubt clearly. I was just thinking that only first row of test1 that has value 1 will join with only first row of test2 that has value 1. But that's wrong. It will find all 1's in other table and join with it. Thanks. – bob Mar 29 '19 at 10:19