Mysql – Relation Subtraction wrong result

MySQL

I have 2 queries:

Query1:

SELECT DISTINCT maker FROM Product WHERE type='laptop'

Gives result {A, B, E, F, G}

and

Query2:

SELECT DISTINCT maker FROM Product WHERE type='pc'

Gives result {A, B, C, D, E}

The question is:
How can I get query1 - query2 = {A, B, E, F, G} - {A, B, C, D, E} = {F, G}

I tried:

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='b'
);

But it gave me empty record. Sorry I'm new with MySQL

Best Answer

You might use NOT IN instead:

SELECT DISTINCT maker 
FROM Product 
WHERE type='pc' 
  AND maker  NOT IN
 (
    SELECT maker 
    FROM Product
    WHERE type='b'
      AND maker IS NOT NULL -- to prevent empty results
 );

Caution: This will not work correctly with a NULL maker.

The recommended solution is NOT EXISTS, but you need a Correlated Subquery:

SELECT DISTINCT maker 
FROM Product AS p1
WHERE type='pc'
  AND NOT EXISTS
 (
    SELECT maker
    FROM Product AS p2
    WHERE type='b'
      AND p1.maker = p2.maker -- correlation to the outer SELECT
 );

If MySQL supported EXCEPT it would be a simple

SELECT maker FROM Product WHERE type='pc'
EXCEPT
SELECT maker FROM Product WHERE type='b';
Related Question