Can anyone explain join behavior with Null-CodePudding

According to my knowledge, Null is not comparable with anything. But in below example getting result in different way. Can someone help me to understand?

Create table t1 (c1 number);

 Insert into T1(C1) Values(1);
 Insert into T1(C1) Values(NULL);
 Insert into T1(C1) Values(1);
 Insert into T1(C1) Values(NULL);
 Insert into T1(C1) Values(1);

create table t2 (c2 number);

 Insert into T2(C2) Values(1);
 Insert into T2(C2) Values(NULL);
 Insert into T2(C2) Values(1);
 Insert into T2(C2) Values(NULL);
 Insert into T2(C2) Values(1);
 Insert into T2(C2) Values(NULL);
 Insert into T2(C2) Values(1);

 select * from t1;

1
NULL
1
NULL
1

select * from t2

1
NULL
1
NULL
1
NULL
1
select * from t1 inner join t2 on(t1.c1=t2.c2);

C1   C2
1    1
1    1
1    1
1    1
1    1
1    1
1    1
1    1
1    1
1    1
1    1
1    1

Please find the attached image for result set.

CodePudding user response：

In terms of inner join or (default join in SQL Server) NULL doesn't have much significance. You are getting 12 records because 3 records from table t1 and getting multiplied by 4 records table t2. You will different result with distinct values.

Null makes an impact if it is left/right/cross join. Here are some other queries. Consider below queries as well.

select * from t1 inner join t2 on(t1.c1=t2.c2); --12
select * from t1 left join t2 on(t1.c1=t2.c2); --14 ( 2 null records considered from table t1)
select * from t1 right join t2 on(t1.c1=t2.c2); --15 ( 3 -null records considered from table t2)
select * from t1 cross join t2 --35 (5x7)

CodePudding user response：

Refer here to understand more about joins

Video here