Compare two queries to see if their results are identical

Scenario - I would like to compare two queries to find out if their results
are identical. The first query returns all the values stored in an invoice
table for a given invoice no. The second query returns all the values
stored in a table holding booking information.
E.g.
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
InvoiceDetails WHERE InvoiceNo = 12345
SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
BookingDetails WHERE BookingNo = 20
I need to know whether the results from the two queries are identical. One
way I can think of doing this is to perform two EXISTS queries, the first
one finding a row that does not exist in InvoiceDetails that does exist in
BookingDetails and a second query that finds a row that does not exist in
BookingDetails that does in InvoiceDetails. Then if one or the other tests
finds a row the answer is they are different otherwise they are the same.
But is there a better way to perform this test - can be be done is one test?
Thanks.One way is to do a full join and then look for NULLs on either side.
Another method is to UNION the two queries and in the result GROUP BY
<all columns> HAVING COUNT(*)=1 to show any mismatches. This second
method has the advantage that you can compare on the basis of NULL=NULL
if you wish.
David Portas
SQL Server MVP
--|||The first way is to create a cursor with your first query and within to
compare it with your second result, as you specify it with IF EXISTS.
You should also do the opposite operation with the second resultset.
But you should consider that cursor are not efiiciency for large table. So
you could also create tempory table from you resulstet and compare them
trough join operation,
Laurent
"Chris" <cw@.community.nospam> a crit dans le message de news:
eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||Try:
if exists (
select *
from InvoiceDetails as a inner join BookingDetails as b
on a.InvoiceNo = 12345 and b.BookingNo = 20
and a.Quantity = b.Quantity
and a.[Description] = b.[Description]
and a.UnitPrice = b.UnitPrice
and a.TaxPercentage = b.TaxPercentage
and a.Tax = b.Tax
and a.Price = b.Price
)
print 'identical'
else
print 'not identical'
go
AMB
"Chris" wrote:

> Scenario - I would like to compare two queries to find out if their result
s
> are identical. The first query returns all the values stored in an invoic
e
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical. On
e
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other test
s
> finds a row the answer is they are different otherwise they are the same.
> But is there a better way to perform this test - can be be done is one tes
t?
> Thanks.
>
>|||Can't you do a FULL OUTER JOIN and see if either side is NULL?
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>|||CREATE TABLE #T1(i int, j int)
CREATE TABLE #T2(i int, j int)
insert into #t1 values(1,2)
insert into #t2 values(1,2)
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- identical
--
1
update #t2 set j=3
select count(*) from(
select * from #t1
union
select * from #t2
) t
-- different
--
2
drop table #t1
drop table #t2|||USE LRUKUVL
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL DROP TABLE #Table1
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL DROP TABLE #Table2
CREATE TABLE
#Table1
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
CREATE TABLE
#Table2
(
ID INT PRIMARY KEY CLUSTERED
, Data1 TINYINT NULL
, Data2 CHAR(1) NULL
)
INSERT INTO #Table1
SELECT 1 , 4 , 'A'
UNION ALL SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , NULL , 'C'
UNION ALL SELECT 4 , 1 , 'D'
UNION ALL SELECT 5 , 1 , 'D'
UNION ALL SELECT 7 , 5 , NULL
INSERT INTO #Table2
SELECT 2 , 3 , 'B'
UNION ALL SELECT 3 , 2 , 'C'
UNION ALL SELECT 4 , 1 , NULL
UNION ALL SELECT 5 , 1 , 'E'
UNION ALL SELECT 6 , 1 , 'F'
UNION ALL SELECT 7 , 5 , NULL
/* return records that are different */
/* only id=2 and id=7 are equal - others are returned */
/* write a UDF to compaire a.data = b.data checking for equality, and is
nulls differences */
SELECT
*
FROM
#Table1 a
FULL OUTER JOIN
#Table2 b
ON
a.id = b.id
WHERE
a.id IS NULL
OR
b.id IS NULL
OR
( a.data1 IS NULL AND b.data1 IS NOT NULL )
OR
( a.data1 IS NOT NULL AND b.data1 IS NULL )
OR
( a.data1 != b.data1 )
OR
( a.data2 IS NULL AND b.data2 IS NOT NULL )
OR
( a.data2 IS NOT NULL AND b.data2 IS NULL )
OR
( a.data2 != b.data2 )
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
results
> are identical. The first query returns all the values stored in an
invoice
> table for a given invoice no. The second query returns all the values
> stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
One
> way I can think of doing this is to perform two EXISTS queries, the first
> one finding a row that does not exist in InvoiceDetails that does exist in
> BookingDetails and a second query that finds a row that does not exist in
> BookingDetails that does in InvoiceDetails. Then if one or the other
tests
> finds a row the answer is they are different otherwise they are the same.|||Thanks to everyone for the replies
Chris
"Chris" <cw@.community.nospam> wrote in message
news:eli7vkbyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Scenario - I would like to compare two queries to find out if their
> results are identical. The first query returns all the values stored in
> an invoice table for a given invoice no. The second query returns all the
> values stored in a table holding booking information.
> E.g.
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> InvoiceDetails WHERE InvoiceNo = 12345
> SELECT Quantity, [Description], UnitPrice, TaxPercentage, Tax, Price FROM
> BookingDetails WHERE BookingNo = 20
> I need to know whether the results from the two queries are identical.
> One way I can think of doing this is to perform two EXISTS queries, the
> first one finding a row that does not exist in InvoiceDetails that does
> exist in BookingDetails and a second query that finds a row that does not
> exist in BookingDetails that does in InvoiceDetails. Then if one or the
> other tests finds a row the answer is they are different otherwise they
> are the same.
> But is there a better way to perform this test - can be be done is one
> test?
> Thanks.
>