Join ,union and union all with null values in tables?

--demo of join,union and union all

NOTE: Tables having null values

create table join1(id int,name varchar(50))

insert into join1 values(1,'jana'),(2,'sundu'),(null,null),(4,'null')

create table join2(id int,name varchar(50))

insert into join2 values(1,'jana'),(2,null),(3,null),(4,'null')

select * from join1 j1

inner join

join2 j2 on

j1.id=j2.id

Result

id            name    id            name

1              jana       1              jana

3              sundu   3              NULL

4              null         4              null

--union

select * from join1 j1

union

select * from join2 j2

--Note:union will give both matching rows and nonmatching rows only once

--i.e distinct values

Result

id    name

NULL  adi

1     jana

2     NULL

3     NULL

3     sundu

4     null

--union all

select * from join1 j1

union all

select * from join2 j2

--it gives all values from both tables

Result

id            name

1              jana

NULL     adi

3              sundu

4              null

1              jana

2              NULL

3              NULL

4              null