Menu
I am replacing an old untidy SQL query with a new version, because it was failing to include several rows it should have.
The new query certainly includes these missing rows, but I want to be entirely sure that it also includes all of the rows from the original query.
The two queries use entirely different tables. Each query is ~14000 rows.
Is there a query I can write that checks whether QueryA contains any rows that QueryB does not have?
560
Comparing the Results of the Two Queries The solution to this is very simple. Run both queries using a UNION to combine the results! The UNION operator returns unique records. If the two results sets are identical the row count will remain the same as the original query.
Run the queries and compare logical reads for the various tables and execution times. @CombatCaptain You can also stack the comparing queries together in SSMS and press CTRL+M (include actual execution plan) and then F5 .
SQL is a declarative language, and assignments are not typically made in SQL queries themselves. As a result, SQL doesn't have the problem of ambiguity of = meaning either assignment or equality check. As a result, there is no problem with using = to check equality.
You could do something like this.
Select * FROM
(
QUERY A GOES HERE
) AS A
LEFT JOIN
(
QUERY B GOES HERE
) AS B
ON A.Col1=B.Col1 AND A.Col2=B.Col2 ....
WHERE B.Col1 IS NULL
You can either include all the columns in the "on clause" or you can just include the columns you need to ensure the rows are the same, such as the primary key.
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
