Compare Existence Of The Same Field A Based On Field Batch [closed]

A table has fields

• code
• batch

I want to know what code(s) are in batch 1 but not in batch 2. What is the best SQL statement for this?

Answer

There are multiple options as follows:

Using NOT EXISTS:

SELECT DISTINCT T.CODE FROM YOUR_TABLE T
 WHERE T.BATCH = 'BATCH1'
   AND NOT EXISTS (SELECT 1 FROM YOUR_tABLE TT
                    WHERE TT.CODE = T.CODE 
                      AND TT.BATCH = 'BATCH2');

Using LEFT JOIN:

SELECT DISTINCT B1.CODE 
FROM YOUR_tABLE B1 LEFT JOIN YOUR_tABLE B2
  ON B1.CODE = B2.CODE AND B2.BATCH = 'BATCH2'
WHERE B1.BATCH = 'BATCH1' AND B2.CODE IS NULL;