Hi @glennyboy ,
When using left join, right join, full (outer) join, it is possible to return NULL value, while (inner) join, cross join will not return NUll value.
The following example is the difference between these joins, please refer to:
--join
create table a
(id int,
name char(20))
create table b
(id int,
name varchar(200))
insert into a values(1,'Tom'),(2,'Jane'),(3,'John'),(4,'Eyre'),(5,'Andy')
insert into b values(1,'Jane'),(2,'Alva'),(3,'Tom'),(4,'Alice'),(5,'Eyre')
select * from a
select * from b
select * from a inner join b on a.name=b.name
select * from a left join b on a.name=b.name
select * from a right join b on a.name=b.name
select * from a full outer join b on a.name=b.name
When using full join, all information of table a and table b will be returned. When there is no value that meets the on condition, a null value will be returned.
select * from a cross join b
For more details, please refer to:Joins (SQL Server)
If this doesn't solve your problem,please publish the table with the data and the output you want, so that the experts will get a right direction and can do some tests, which will help you solve your problem quickly.
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.
Best Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.