sql - How can I extract the difference from two queries?

Question

Using SQL Server.

I have one query which outputs results, and another that does the same. Id like to output what the is not in the other:

select distinct 
v_GS_PC_BIOS.SerialNumber0
from v_GS_PC_BIOS
JOIN v_R_System ON v_R_System.ResourceID  = v_GS_PC_BIOS.ResourceID
WHERE v_GS_PC_BIOS.ResourceID NOT IN 
(
    SELECT distinct v_R_System.ResourceID from v_r_system
)

OR

Select distinct
v_R_System.ResourceID
from v_R_System
WHERE v_R_System.ResourceID NOT IN 
(
    select distinct 
    v_GS_PC_BIOS.SerialNumber0
    from v_GS_PC_BIOS
    JOIN v_R_System ON v_R_System.ResourceID  = v_GS_PC_BIOS.ResourceID
)

I am getting 0 results

question from:https://stackoverflow.com/questions/65891758/how-can-i-extract-the-difference-from-two-queries

Answer 1

One way to compare table contents

Lets say you have 2 tables

Cars: modelid, manufacturerid, ... some more car related columns

Airplanes: modelid, manufacturerid, ... some more airplane related columns

Task: determine, which car manufacturers don't make airplanes, and vise-versa.

SELECT c.manufacturerName, 'only cars' as specialization
FROM cars c LEFT JOIN
     airplanes a ON c.manufacturerid = a.manufacturerid
WHERE a.manufacturerid IS NULL
UNION ALL
SELECT a.manufacturerName, 'only airplanes' as specialization
FROM cars c RIGHT JOIN
     airplanes a ON c.manufacturerid = a.manufacturerid
WHERE c.manufacturerid IS NULL

Results here should be like

Mazda, only cars
Toyota, only cars
Airbus, only airplanes

And companies like these should not show because their record will be in both tables

Honda, Mitsubishi, Saab, Ford, Fiat