How to compare two tables without losing unmatched records

Question

How can I join two tables on a given column without losing records from either table when the column is unmatched in one or the other.

i.e.: given the code below how can I join and select from @Port to @Model in such a way that I get the results in @Result?

DECLARE @Port as TABLE(PortID int,SecID int,Pct float)

DECLARE @Model as TABLE(ModelID int,SecID int,Pct float)

DECLARE @Result as TABLE(PortID int,ModelID int,SecID int,PctPort float,PctModel float);

INSERT INTO @Port(PortID,SecID,Pct)

VALUES (1,1,.25)

,(1,2,.25)

,(1,3,.5)

,(2,1,.5)

,(2,2,.25)

,(2,4,.25)

,(3,4,.25)

,(3,3,.25)

,(3,5,.25)

,(3,6,.25);

INSERT INTO @Model(ModelID,SecID,Pct)

VALUES (1,2,.25)

,(1,3,.25)

,(1,4,.5)

,(2,1,.5)

,(2,2,.25)

,(2,3,.25)

,(3,1,.25)

,(3,4,.25)

,(3,5,.25)

,(3,6,.25);

INSERT INTO @Result(PortID,ModelID,SecID,PctPort,PctModel)

VALUES (1,1,1,0.25,NULL)

,(1,1,2,0.25,0.25)

,(1,1,3,0.5,0.25)

,(1,1,4,NULL,0.5)

,(1,2,1,0.25,0.5)

,(1,2,2,0.25,0.25)

,(1,2,3,0.5,0.25)

,(1,3,1,0.25,0.25)

,(1,3,2,0.25,NULL)

,(1,3,3,0.5,NULL)

,(1,3,4,NULL,0.25)

,(1,3,5,NULL,0.25)

,(1,3,6,NULL,0.25)

,(2,1,1,0.5,NULL)

,(2,1,2,0.25,0.25)

,(2,1,3,NULL,0.25)

,(2,1,4,0.25,0.5)

,(2,2,1,0.5,0.25)

,(2,2,2,0.25,0.25)

,(2,2,3,NULL,0.25)

,(2,2,4,0.25,NULL)

,(2,3,1,0.5,0.25)

,(2,3,2,0.25,NULL)

,(2,3,4,0.25,0.25)

,(2,3,5,NULL,0.25)

,(2,3,6,NULL,0.25)

,(3,1,2,NULL,0.25)

,(3,1,3,0.25,0.25)

,(3,1,4,0.25,0.5)

,(3,1,5,0.25,NULL)

,(3,1,6,0.25,NULL)

,(3,2,1,NULL,0.5)

,(3,2,2,NULL,0.25)

,(3,2,3,0.25,0.25)

,(3,2,4,0.25,NULL)

,(3,2,5,0.25,NULL)

,(3,2,6,0.25,NULL)

,(3,3,1,NULL,0.25)

,(3,3,3,0.25,NULL)

,(3,3,4,0.25,0.25)

,(3,3,5,0.25,0.25)

,(3,3,6,0.25,0.25);

SELECT * FROM @Port

SELECT * FROM @Model

SELECT * FROM @Result

Answer 1

Here is the query you are looking for:

DECLARE @Port as TABLE(PortID int,SecID int,Pct float)
DECLARE @Model as TABLE(ModelID int,SecID int,Pct float)
DECLARE @Result as TABLE(PortID int,ModelID int,SecID int,PctPort float,PctModel float);
INSERT INTO @Port(PortID,SecID,Pct)
VALUES (1,1,.25)
,(1,2,.25)
,(1,3,.5)
,(2,1,.5)
,(2,2,.25)
,(2,4,.25)
,(3,4,.25)
,(3,3,.25)
,(3,5,.25)
,(3,6,.25);
INSERT INTO @Model(ModelID,SecID,Pct)
VALUES (1,2,.25)
,(1,3,.25)
,(1,4,.5)
,(2,1,.5)
,(2,2,.25)
,(2,3,.25)
,(3,1,.25)
,(3,4,.25)
,(3,5,.25)
,(3,6,.25);
INSERT INTO @Result(PortID,ModelID,SecID,PctPort,PctModel)
VALUES (1,1,1,0.25,NULL)
,(1,1,2,0.25,0.25)
,(1,1,3,0.5,0.25)
,(1,1,4,NULL,0.5)
,(1,2,1,0.25,0.5)
,(1,2,2,0.25,0.25)
,(1,2,3,0.5,0.25)
,(1,3,1,0.25,0.25)
,(1,3,2,0.25,NULL)
,(1,3,3,0.5,NULL)
,(1,3,4,NULL,0.25)
,(1,3,5,NULL,0.25)
,(1,3,6,NULL,0.25)
,(2,1,1,0.5,NULL)
,(2,1,2,0.25,0.25)
,(2,1,3,NULL,0.25)
,(2,1,4,0.25,0.5)
,(2,2,1,0.5,0.25)
,(2,2,2,0.25,0.25)
,(2,2,3,NULL,0.25)
,(2,2,4,0.25,NULL)
,(2,3,1,0.5,0.25)
,(2,3,2,0.25,NULL)
,(2,3,4,0.25,0.25)
,(2,3,5,NULL,0.25)
,(2,3,6,NULL,0.25)
,(3,1,2,NULL,0.25)
,(3,1,3,0.25,0.25)
,(3,1,4,0.25,0.5)
,(3,1,5,0.25,NULL)
,(3,1,6,0.25,NULL)
,(3,2,1,NULL,0.5)
,(3,2,2,NULL,0.25)
,(3,2,3,0.25,0.25)
,(3,2,4,0.25,NULL)
,(3,2,5,0.25,NULL)
,(3,2,6,0.25,NULL)
,(3,3,1,NULL,0.25)
,(3,3,3,0.25,NULL)
,(3,3,4,0.25,0.25)
,(3,3,5,0.25,0.25)
,(3,3,6,0.25,0.25);
;with ctePort
as (
select distinct PortId from @Port
)
, cteModel
as (
select distinct ModelId from @Model
)
, cteSec
as (
select   SecId from @Port
union
select   SecId from @Model
)
,myFinal as (
select m1.PortId,m2.ModelId,m3.SecId 
from ctePort m1,cteModel m2,cteSec m3
)
select 
f.PortID,f.ModelID,f.SecID
 ,p.pct PctPort,m.pct as PctModel 
 from myFinal f 
 left join  @Port p on  f.PortId=p.PortId and f.SecId=p.SecId
 Left join  @Model m on  f.ModelId=m.ModelId and f.SecId=m.SecId
 where Not(p.pct is null and m.pct is null)
 Order by f.PortID,f.ModelID,f.SecID
 SELECT * FROM @Result

Answer 2

Dear David Kingston, Pl refer below sql blog. hope this will give some details.

https://learnsql.com/blog/sql-join-unmatched-rows/#:~:text=Based%20on%20your%20needs%2C%20you,rows%20from%20the%20other%20table.

Answer 3

Hi @david kingston

You can use two cross joins to get all combinations of the PortID, ModelID, and SecID columns.

DECLARE @Port as TABLE(PortID int,SecID int,Pct float)
DECLARE @Model as TABLE(ModelID int,SecID int,Pct float);
INSERT INTO @Port(PortID,SecID,Pct)
VALUES (1,1,.25)
,(1,2,.25)
,(1,3,.5)
,(2,1,.5)
,(2,2,.25)
,(2,4,.25)
,(3,4,.25)
,(3,3,.25)
,(3,5,.25)
,(3,6,.25);
INSERT INTO @Model(ModelID,SecID,Pct)
VALUES (1,2,.25)
,(1,3,.25)
,(1,4,.5)
,(2,1,.5)
,(2,2,.25)
,(2,3,.25)
,(3,1,.25)
,(3,4,.25)
,(3,5,.25)
,(3,6,.25);
with T1 as(
  select SecID from @Port 
  union 
  select SecID from @Model)
select distinct A.PortID,B.ModelID,C.SecID from @Port as A cross join @Model as B cross join T1 as C;

Next, you can use Jingyang Li's method to join the original table twice and obtain the corresponding Pct according to PortID, SecID, ModelID, and SecID.

Since not all SecIDs have two corresponding Pcts, some are null values, it is most appropriate to use a left outer join. I can't think of another way to achieve the same connection effect.

Best regards,

Percy Tang