Compare Existence Of The Same Field A Based On Field Batch

Question

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?

Use `WHERE NOT EXISTS` with correlated subquery. Or use grouping and `HAVING` with conditional sums. Or 2 copies and `LEFT JOIN WHERE IS NULL`. *What is the best SQL statement for this?* Some of mentioned above, depends on a lot of table statistic parameters. Or even maybe some another... — Akina, Dec 09 '20 at 07:42

Popeye · Accepted Answer · 2020-12-09T08:01:31.893

3

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;

edited Dec 09 '20 at 08:01

answered Dec 09 '20 at 07:57

Popeye

35,427
4
10
31

According to https://stackoverflow.com/questions/8386280/minus-operator-in-mysql, MySQL does not support `MINUS` - has this changed in the meantime? – Nico Haase Dec 09 '20 at 08:01
1

Ok, Got it. Updated the answer!! – Popeye Dec 09 '20 at 08:01
1

I tested the left join as I'm comfortable using LEFT JOINS. Thanks for the quick replies. – Ben Dec 09 '20 at 08:51

Compare Existence Of The Same Field A Based On Field Batch

1 Answers1