Help with FULL JOIN Query - Records from both tables

Question

Hi:

I need help with FULL JOIN Query - I want the output to display all records from @tbl1 and @tbl2 but exclude the records where SType = 'CASH'. I am using the FULL Join query, but it is not giving the SAmount column details it also add another NULL record.

DECLARE @tbl1 TABLE  
(SID int,  
 SName varchar(10),  
 SType varchar(10),  
 SAmount float)  
    
DECLARE @tbl2 TABLE  
(SID int,  
 SName varchar(10),  
 SType varchar(10),  
 SAmount float)  
  
  
INSERT INTO @tbl1 VALUES (1001,'ABCDE','CASH',20.00)  
INSERT INTO @tbl1 VALUES (1002,'XYZ','CASH',40.00)  
INSERT INTO @tbl2 VALUES (1004,'XYZ','NSD',80.00)  
INSERT INTO @tbl1 VALUES (1008,'PPPPPP','TRD',75.00)  
  
INSERT INTO @tbl2 VALUES (1003,'ABCDE','UST',100.00)  
INSERT INTO @tbl2 VALUES (1004,'XYZ','NSD',30.00)  
INSERT INTO @tbl2 VALUES (1006,'PQRS','CASH',100.00)  
INSERT INTO @tbl2 VALUES (1007,'ZZZZZ','CASH',30.00)  
  
  
----OUTPUT  
SELECT   
t.SID,  
t.SName,  
t.SAmount + s.SAmount AS Amount     
FROM @tbl2 t  
FULL JOIN @tbl1 s  
ON t.SID = s.SID  
WHERE (t.SType NOT IN ('CASH')  
      or s.SType NOT IN ('CASH'))  

EXPECTED OUTPUT:

Answer 1

You are returning null because you are assuming tbl1 holds the value however, tbl1 could hold it since this is a full join, not a left join. I assume your line 16 was an insert to tbl1 so I changed it.

use [gsgui]
 DECLARE @tbl1 TABLE
 (SID int,
  SName varchar(10),
  SType varchar(10),
  SAmount DECIMAL(9,2))

 DECLARE @tbl2 TABLE
 (SID int,
  SName varchar(10),
  SType varchar(10),
  SAmount DECIMAL(10,2))


 INSERT INTO @tbl1 VALUES (1001,'ABCDE','CASH',20.00)
 INSERT INTO @tbl1 VALUES (1002,'XYZ','CASH',40.00)
 INSERT INTO @tbl1 VALUES (1004,'XYZ','NSD',80.00)
 INSERT INTO @tbl1 VALUES (1008,'PPPPPP','TRD',75.00)

 INSERT INTO @tbl2 VALUES (1003,'ABCDE','UST',100.00)
 INSERT INTO @tbl2 VALUES (1004,'XYZ','NSD',30.00)
 INSERT INTO @tbl2 VALUES (1006,'PQRS','CASH',100.00)
 INSERT INTO @tbl2 VALUES (1007,'ZZZZZ','CASH',30.00)


 SELECT 
 ISNULL(t.SID,s.SID) SID,
 ISNULL(t.SName,s.SName) SName,
 ISNULL(t.SType,s.SType) SType,
 ISNULL(t.SAmount,0) + ISNULL(s.SAmount,0) Amount
 FROM @tbl2 t
 FULL JOIN @tbl1 s
 ON t.SID = s.SID 
 WHERE (t.SType <> ('CASH') OR s.SType <> ('CASH'))

You can see where your nulls are affecting you by simply selecting all and reviewing the null columns:

 SELECT *
 FROM @tbl2 t
 FULL JOIN @tbl1 s
 ON t.SID = s.SID 
 WHERE (t.SType <> ('CASH') OR s.SType <> ('CASH'))

Answer 2

I have found that when doing FULL JOINs it is almost necessarily to do the filtering before the join:

SELECT isnull(t.SID, s.SID) AS Sid,
       isnull(t.SName, s.SName) AS SName,
       isnull(t.SType, s.SType) AS SType,
       isnull(t.SAmount, 0) + isnull(s.SAmount, 0) AS Amount   
FROM      (SELECT * FROM @tbl2 WHERE Stype NOT IN ('CASH')) t
FULL JOIN (SELECT * FROM @tbl1 WHERE Stype NOT IN ('CASH')) s
     ON t.SID = s.SID

The same really applies to other joins as well, but with these it just doesn't matter. in the same way.

Then again, for this particular problem, it is possible that UNION ALL serves you better:

SELECT SID, SName, SType, SUM(SAmount)
FROM   (SELECT SID, Sname, SType, SAmount
        FROM   @tbl1
        UNION ALL
        SELECT SID, Sname, SType, SAmount
        FROM   @tbl2) AS u
WHERE  SType <> 'CASH'
GROUP BY SID, SName, SType