Is there a smart way to implement a logical XOR in T-SQL (difference between two requests)

Question

As part of system verification, I am trying to compare the results between two queries (simplified versions below)

-- Reference query, writing the criteria myself as part of the verification process
SELECT p.Id, p.FullName
FROM Person p
WHERE -- some criteria

And

-- Data Query, simply reading what my system under tests thinks of the same criteria
SELECT p.Id, p.FullName
FROM Person p
  RIGHT JOIN PersonsThatMatchCriteria pmc ON ...

I want to be sure that they return teh same results, and in case not, I want to display them for analysis.

((REFERENCE QUERY)
EXCEPT
(DATA QUERY))
  UNION
((DATA QUERY)
EXCEPT
(REFERENCE QUERY))

As my process is quite poor in tools and automation, I type these requests mostly by hand. I hate having to retype / copy-paste the queries twice, as it is error prone.

So the question is simple: Is there an easy syntax (or simple tool) that would allow me to write something like

(REFERENCE QUERY)
  XOR
(DATA QUERY)

I have found a hack that works well but is still heavy. Is there better (simpler to write)?

DECLARE @ShouldBeIncluded TABLE(Id INT); INSERT INTO @ShouldBeIncluded  
    SELECT p.Id, p.FullName
    FROM Person p
    WHERE ...

DECLARE @ActuallyIncluded TABLE(Id INT); INSERT INTO @ActuallyIncluded
    SELECT p.Id, p.FullName
    FROM Person p
      RIGHT J

SELECT p.Id, p.FullName
FROM Person p
WHERE p.Id IN (         SELECT Id FROM @ActuallyIncluded
                    EXCEPT  SELECT Id FROM @ShouldBeIncluded    )
    OR p.Id IN (            SELECT Id FROM @ShouldBeIncluded
                    EXCEPT  SELECT Id FROM @ActuallyIncluded    )

Answer 1

As I understand your need it is to display any expected results which were not returned by the actual query and any results from the actual query which were not expected.

I would use two tables, the first table holds the expected data and includes a checksum value across all the columns of intrest, the second table is the results of the query on the actual system and should include a checksum column which will be updated after the actual query executes.

Evaluate the example below to see if it fits you need.

declare @lt_ExpectedResults table (lER_PersonName varchar(20), lER_PersonStuff varchar(20), lER_Chksum bigint)
    Insert @lt_ExpectedResults  (lER_PersonName, lER_PersonStuff)
    Values   
    ('Bubba1','Stuf1'),
    ('Bubba2','stuff2')
    Update @lt_ExpectedResults set lER_Chksum = CHECKSUM(*)

    declare @lt_ActualResults table (lAR_PersonName varchar(20), lAR_PersonStuff varchar(20), lAR_Chksum bigint)
    Insert @lt_ActualResults   (lAR_PersonName, lAR_PersonStuff)
    Values
    ('Bubba1','Stuf1'),
    ('Bubba3','stuff2')
    Update @lt_ActualResults set lAR_Chksum = CHECKSUM(*)

    select * from @lt_ExpectedResults
    select * from @lt_ActualResults

    Select * from @lt_ExpectedResults
    full outer join @lt_ActualResults
    on lAR_Chksum = lER_Chksum
     where   lAR_Chksum is NULL or lER_Chksum is NULL

You should be aware there are cases when the CHECKSUM value will be identical from un-identical source data. Review StackOverflow and this link for more information

CheckSum

Answer 2

You could use a CTE:

WITH ComplexQuery1(Col1, Col2) AS (
SELECT Col1, Col2
FROM ...
),
ComplexQuery2(Col1, Col2) AS (
SELECT Col1, Col2
FROM ...
),
Except1(Col1, Col2) AS(
SELECT * FROM ComplexQuery1
EXCEPT
SELECT * FROM ComplexQuery2
),
Except2(Col1, Col2) AS(
SELECT * FROM ComplexQuery2
EXCEPT
SELECT * FROM ComplexQuery1
)
SELECT * FROM Except1
UNION ALL
SELECT * FROM Except2;