MySQL difference in two columns

I h开发者_JS百科ave a table with two columns A and B, they contain values of same domain, let's say I want to find the values that only appears in A but not in B, i.e

A has 1,2,3 B has 2,3

so my answer will be A - B = {1}

so the SQL query I wrote is

SELECT DISTINCT(A) 
  FROM DB
 WHERE A NOT IN (SELECT DISTINCT(B) 
                   FROM DB);

It doesn't work and taking forever to response (8000 records approx), does anyone have a solution similar to the above? Thanks.

I found some thread e.g MySQL: difference of two result sets but doesn't really do what I want to do thou.

How does the below perform? If you're getting poor performance, ensure you've indexes on both columns.

SELECT DISTINCT DB1.A
FROM            DB DB1
LEFT JOIN       DB DB2
ON              DB2.B = DB1.A
WHERE           DB2.B IS NULL

SELECT DISTINCT d1.A 
FROM DB d1 LEFT JOIN DB d2 ON d1.A=d2.B 
WHERE de.B IS NULL

ALTER TABLE DB ADD INDEX a_idx(A);

ALTER TABLE DB ADD INDEX b_idx(B);

When working with MySQL you have to remember that it has no ways of doing this query fast without indexes, because it does not support :

• hash aggregation
• hash joins
• merge joins
• hash IN() checks
• etc
• do not think about doing this :

    EXPLAIN ANALYZE SELECT DISTINCT a FROM t WHERE a NOT IN (SELECT b FROM t);
    HashAggregate  (actual time=7.283..7.428 rows=827 loops=1)
    ->  Seq Scan on t (actual time=4.159..6.778 rows=1697 loops=1)
        Filter: (NOT (hashed SubPlan 1))
          SubPlan 1
          ->  Seq Scan on t  (actual time=0.006..1.497 rows=8000 loops=1)
     Total runtime: 6.840 ms

Also MySQL (depending on the version) is more or less braindead regarding subqueries, although it tend to get better.

• create an index on column A
• create an index on column B

Try :

SELECT DISTINCT t1.a 
FROM table t1 
LEFT JOIN table t2 ON (t1.a=t2.b)
WHERE t2.b IS NULL

Or :

SELECT DISTINCT a 
FROM table t1 
WHERE NOT EXISTS( 
 SELECT * FROM t2 WHERE t2.b=t1.a
)