SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TableA (
    Date DATE,
    BatchID INT,
    ProductName VARCHAR(10),
    Change INT
)

INSERT INTO TableA
VALUES
    ('2024-10-05', 1, 'PN1', 1),
    ('2024-10-05', 1, 'PN2', 2),
    ('2024-10-05', 2, 'PN1', 2)

CREATE TABLE TableB (
    Date DATE,
    BatchID INT,
    ProductName VARCHAR(10)
)

INSERT INTO TableB
VALUES
    ('2024-10-05', 1, 'PN1'),
    ('2024-10-05', 1, 'PN2'),
    ('2024-10-05', 1, 'PN3'),
    ('2024-10-05', 2, 'PN1'),
    ('2024-10-05', 2, 'PN2')

8 rows affected

-- Using FULL OUTER JOIN and COALESCE()
SELECT
    COALESCE(A.Date, B.Date) AS Date,
    COALESCE(A.BatchID, B.BatchID) AS BatchID,
    COALESCE(A.ProductName, B.ProductName) AS ProductName,
    COALESCE(A.Change, 0) AS Change
FROM TableA A
FULL OUTER JOIN TableB B
    ON B.Date = A.Date
    AND B.BatchID = A.BatchID
    AND B.ProductName = A.ProductName


Date
BatchID
ProductName
Change


2024-10-05
1
PN1
1

2024-10-05
1
PN2
2

2024-10-05
2
PN1
2

2024-10-05
1
PN3
0

2024-10-05
2
PN2
0


-- Using UNION ALL and WHERE NOT EXISTS(...)
SELECT A.Date, A.BatchID, A.ProductName, A.Change
FROM TableA A
UNION ALL
SELECT B.Date, B.BatchID, B.ProductName, 0 AS Change
FROM TableB B 
WHERE NOT EXISTS (
    SELECT *
    FROM TableA A
    WHERE A.Date = B.Date
    AND A.BatchID = B.BatchID
    AND A.ProductName = B.ProductName
)


Date
BatchID
ProductName
Change


2024-10-05
1
PN1
1

2024-10-05
1
PN2
2

2024-10-05
2
PN1
2

2024-10-05
1
PN3
0

2024-10-05
2
PN2
0

Date	BatchID	ProductName	Change
2024-10-05	1	PN1	1
2024-10-05	1	PN2	2
2024-10-05	2	PN1	2
2024-10-05	1	PN3	0
2024-10-05	2	PN2	0