Q:

Faster way compare changes between SQL sub-queries?

I need to check for the change in the color of cats between two dates.
I have the query below:
WITH
cats_prior AS (SELECT IDTAG,Color FROM  CATS WHERE APPOINTMENT = '06/30/2019'),
cats_now AS (SELECT IDTAG,Color FROM  CATS WHERE APPOINTMENT = '08/31/2019')

SELECT cats_prior.IDTAG, cats_prior.Color,cats_now.Color
FROM cats_prior
JOIN cats_now on cats_prior.IDTAG = cats_now.IDTAG
WHERE cats_prior.Color != cats_now.Color

It works but it takes 11 minutes and there are around 15 million cats in that table. 
Is there another way to do this? or a way to make this faster?
This is SQL Server.

A:

I would try aggregation with a HAVING clause:
SELECT IDTAG
FROM  CATS
WHERE APPOINTMENT IN ('2019-08-31', '2019-06-30')
GROUP BY IDTAG
HAVING MIN(COLOR) <> MAX(COLOR);

An index on CATS(APPOINTMENT, IDTAG, COLOR) would help.  This index might also speed up your version of the query.

A:

Just another way to try the same thing:
select distinct cp.IDTAG
from CATS cp
inner join CATS cn
   on cp.IDTAG = cn.IDTAG
   and cp.color <> cn.color
where cp.APPOINTMENT = '06/30/2019'
   and cn.APPOINTMENT = '08/31/2019'

You can check performance against your data.

