Distinct Join to find data that does NOT match - Teradata

Question

really struggling with this... I have written the following code that seems to work and identifies the row ID of 40,000 addresses that match where FrontDoorColour is RED.

SELECT DISTINCT ID
FROM Database.table1
WHERE table1.address = table2.address
AND table1.FrontDoorColour = 'RED'

The problem I have is when I want to reverse this and identify the 10,000 addresses where FrontDoorColour is RED but where the address does NOT match.

I run the same query but swap

WHERE table1.address = table2.address

for

WHERE table1.address <> table2.addres

Instead of generating the 10,000 NON-matching rows, I get a spool space error (2646)

Any suggestions would be greatly appreciated! Thanks

Answer 1

An EXPLAIN output of the second query should yield PRODUCT JOIN and is likely the reason for the spool error you received. The first query may also yield a product join but it may process within your spool allocation. The following SQL should help you find address ids from Table1 where the address is not found in Table2 and the door in Table1 is RED for the address id.

SELECT DISTINCT t1.id
  FROM Database.Table1 t1
 WHERE NOT EXISTS (SELECT 1
                     FROM Database.Table2 t2
                    WHERE t1.address = t2.address)
   AND t1.FrontDoorColour = 'RED';