Getting difference of two counts in SQL

Question

I'm doing some QA in Netezza and I need to compare the counts from two separate SQL statements. This is the SQL that I am currently using

SELECT COUNT(*) AS RECORD_COUNT  
FROM db..EXT_ACXIOM_WUL_FILE  A
LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,mid_key from db..F_EMAIL) B
ON A.MID_KEY=B.MID_KEY
MINUS
SELECT COUNT(*)
FROM db..EXT_ACXIOM_WUL_FILE  A

However, it seems like MINUS doesn't work like that. When the counts match, instead of returning 0, this will return null for Record_count. I basically the record count to be computed as:

record_count=count1-count2

So it is 0 if the counts are equal or the difference otherwise. What is the correct SQL for this?

Answer 1

SELECT
(
SELECT COUNT(*) AS RECORD_COUNT  
FROM db..EXT_ACXIOM_WUL_FILE  A
LEFT JOIN (select distinct CURRENTLY_OPTED_IN_FL,mid_key from db..F_EMAIL) B
ON A.MID_KEY=B.MID_KEY
)  -
(
SELECT COUNT(*)
FROM db..EXT_ACXIOM_WUL_FILE  A
) TotalCount

Oracle's MINUS (EXCEPT in SQL Server) is a whole different animal :)

If you understand UNION and then think sets, you will understand MINUS / EXCEPT