Table A:

Table B:

Id 1 and 2 have matches. I want my SQL query to return

Since it does not have a match

CodePudding user response：

I think you need this. tell me if it solve your problem.

With Matches As (
Select Distinct A.Id
FROM A INNER JOIN B ON A.Id = B.Id AND A.Rent = B.Rent
)
Select * 
From B 
Where B.Id NOT IN (Select Id from Matches)

CodePudding user response：

Hopefully I do understand your requirement correctly.

1. Find list of ID in one table but without matching ID Rent in another table
2. Join back to the original table and list the Rent from both table side by side

with 
A_not_in_B as
(
    -- find list of ID without matching ID   Rent in table B
    select A.ID
    from   A LEFT JOIN B ON A.ID = B.ID AND A.Rent = B.Rent
    group by A.ID
    having MIN(B.Rent) is NULL
),
B_not_in_A as
(
    -- find list of ID without matching ID   Rent in table A
    select B.ID
    from   B LEFT JOIN A ON A.ID = B.ID AND A.Rent = B.Rent
    group by B.ID
    having MIN(A.Rent) is NULL
),
A_Rent as
(
    -- Join back to table A, generate a running number
    select A.ID, A.Rent, RN = row_number() over (order by A.ID, A.Rent)
    from   A_not_in_B N
           inner join A on A.ID = N.ID
),
B_Rent as
(
    -- Join back to table B, generate a running number
    select B.ID, B.Rent, RN = row_number() over (order by B.ID, B.Rent)
    from   B_not_in_A N
           inner join B on B.ID = N.ID
)
-- full outer join to list Rent side by side
select *
from   A_Rent A
       full outer join B_Rent B on  A.ID = B.ID
                                and A.RN = B.RN

db<>fiddle demo